﻿///Form/Modul    : wsEnh2
///Function      : web service untuk SPA enhancement phase 2
///Modified      : 21 sept 2011, add fungsi untuk upload user zip, get user zip, delete user zip terkait multiple pc
///                   jika pindah pc maka user zip di upload untuk kemudian di download di pc baru, jadi user zip sesuai setting dari pc lama 

using System;
using System.ComponentModel;
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.DirectoryServices;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.IO;

namespace Shuba.Web.ws
{
    /// <summary>
    /// Summary description for wsEnh2
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class wsEnh2 : System.Web.Services.WebService
    {
        static Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();
        static string DBServer = ConfigurationManager.AppSettings["db.server"];
        static string DBName = ConfigurationManager.AppSettings["db.name"];
        static string DBUser = ConfigurationManager.AppSettings["db.user"];
        static string DBPass = Encryption.Decrypt(ConfigurationManager.AppSettings["db.pwd"]);
        static string cn = String.Format("Data Source = {0}; Initial Catalog = {1}; User Id = {2}; Password = {3}; Connect Timeout = 30; Min Pool Size = 5", DBServer, DBName, DBUser, DBPass);
        static DbProviderFactory provider = DbProviderFactories.GetFactory("System.Data.SqlClient");

        private static Database CreateDatabase()
        {
            return new GenericDatabase(cn, provider);
        }

        //private void AppendToLog(string log)
        //{
        //    System.Web.HttpContext.Current.Response.AppendToLog(log + " " + DateTime.Now.ToString("dd MMM yyyy hh:mm:ss:fff"));
        //}
        
               
        [WebMethod]
        public byte[] GetAlternate(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                SELECT TOP 1 @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK);

                SELECT	DISTINCT AU.BRANCH_ID, AU.NIP, VU.USER_NAME, AU.ALTERNATE_FOR, 
                        AU.EFF_DATE_FROM, AU.EFF_DATE_TO, AU.IS_ACTIVE
                FROM	TBL_ALTERNATE_UM (NOLOCK) AU 
                        INNER JOIN V_USER (NOLOCK) VU ON AU.NIP = VU.NIP
                WHERE  (CONVERT(VARCHAR(10), AU.EFF_DATE_TO,120) >= CONVERT(VARCHAR(10),@PROCESS_DATE,120)) 
                        AND AU.BRANCH_ID = @UNIT_CODE");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPipelineUM(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT TBL_PIPELINE.ROW_ID,TBL_PIPELINE.UNIT_CODE,TBL_PIPELINE.FULL_NAME,TBL_PIPELINE.JENIS_USAHA
                    ,TBL_PIPELINE.RENCANA_PLAFON,TBL_PIPELINE.MOBILE_PHONE,TBL_PIPELINE.OFFICE_PHONE
                    ,TBL_PIPELINE.RESIDENTIAL_PHONE,TBL_PIPELINE.KODYA_KAB_BUSINESS,TBL_PIPELINE.KECAMATAN_BUSINESS
                    ,TBL_PIPELINE.KELURAHAN_BUSINESS,TBL_PIPELINE.ADDRESS_BUSINESS,TBL_PIPELINE.ZIP_CODE_BUSINESS,TBL_PIPELINE.DATE_VISIT_PLAN
                    ,ISNULL(B.MS_STD_NAME,'') PROSPECT_TYPE,ISNULL(C.MS_STD_NAME,'') DEBTOR_TYPE
                    ,ISNULL(D.CHANNEL_NAME,'') CHANNELING,ISNULL(E.MS_STD_NAME,'') EKSTERNAL
                    ,TBL_PIPELINE.OMZET_1,TBL_PIPELINE.OMZET_2,TBL_PIPELINE.OMZET_3,TBL_PIPELINE.OMZET_4,TBL_PIPELINE.OMZET_5,TBL_PIPELINE.OMZET_6,TBL_PIPELINE.OMZET_7
                    ,TBL_PIPELINE.OMZET_8,TBL_PIPELINE.OMZET_9,TBL_PIPELINE.OMZET_10,TBL_PIPELINE.OMZET_11,TBL_PIPELINE.OMZET_12,TBL_PIPELINE.OMZET_AVERAGE
                    ,ISNULL(B.MS_STD_ID,'') PROSPECT_TYPE_ID, ISNULL(C.MS_STD_ID,'') DEBTOR_TYPE_ID
                    ,ISNULL(D.CHANNEL_ID,'') CHANNEL_ID,ISNULL(E.MS_STD_ID,'') EXTERNAL_ID, TBL_PIPELINE.REFERENSI
                 FROM TBL_PIPELINE_DOWNLOAD (NOLOCK) 
                    INNER JOIN TBL_PIPELINE (NOLOCK) ON TBL_PIPELINE_DOWNLOAD.ROW_ID = TBL_PIPELINE.ROW_ID
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE MS_STD_GRP_ID = 5) AS B ON TBL_PIPELINE.PROSPECT_TYPE = B.MS_STD_ID
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE MS_STD_GRP_ID = 9) AS C ON TBL_PIPELINE.DEBTOR_TYPE = C.MS_STD_ID
                    LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE MS_STD_GRP_ID = 15) AS E ON TBL_PIPELINE.EKSTERNAL = E.MS_STD_ID
                    LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL (NOLOCK)) AS D ON TBL_PIPELINE.CHANNELING = D.CHANNEL_ID
                WHERE (TBL_PIPELINE.UNIT_CODE = @UNIT_CODE) AND (TBL_PIPELINE.IS_DOWNLOADED = '0') AND (TBL_PIPELINE.IS_ASSIGNED = '0')");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public int GetCountPipeline(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT COUNT(1)
	            FROM TBL_PIPELINE_DOWNLOAD (NOLOCK) 
                    INNER JOIN TBL_PIPELINE (NOLOCK) ON TBL_PIPELINE_DOWNLOAD.ROW_ID = TBL_PIPELINE.ROW_ID
                WHERE (TBL_PIPELINE.UNIT_CODE = @UNIT_CODE) AND (TBL_PIPELINE.IS_DOWNLOADED = '0') AND (TBL_PIPELINE.IS_ASSIGNED = '0')");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                object result = db.ExecuteScalar(cmd);
                
                if (result != null) return Convert.ToInt32(result);
            }
            return 0;
        }

        [WebMethod]
        public byte[] GetTopupUM(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT TBL_TOPUP.CIF_NUMBER, TBL_TOPUP.LOAN_NUMBER, TBL_TOPUP.UNIT_CODE, TBL_TOPUP.DEBITOR_NAME
                ,TBL_TOPUP.PRODUCT_CODE, ISNULL(TBL_TOPUP.CURRENT_OS,0) AS CURRENT_OS, ISNULL(TBL_TOPUP.CURRENT_INSTALLMENT,0) AS CURRENT_INSTALLMENT 
                ,ISNULL(TBL_TOPUP.OFFER_PLAFON,0) AS OFFER_PLAFON, ISNULL(TBL_TOPUP.OFFER_INSTALLMENT,0) AS OFFER_INSTALLMENT, ISNULL(TBL_TOPUP.OFFER_INTEREST,0) AS OFFER_INTEREST
                ,ISNULL(TBL_TOPUP.OFFER_TENOR,0) AS OFFER_TENOR,ISNULL(TBL_TOPUP.OFFER_FRESH_MONEY,0) AS OFFER_FRESH_MONEY,TBL_TOPUP.PRIORITY,TBL_TOPUP.JENIS_USAHA
                ,TBL_TOPUP.KODYA_KAB_BUSINESS, TBL_TOPUP.KECAMATAN_BUSINESS, TBL_TOPUP.KELURAHAN_BUSINESS, TBL_TOPUP.ZIP_CODE_BUSINESS
                ,TBL_TOPUP.ADDRESS_BUSINESS, TBL_TOPUP.MOBILE_PHONE, TBL_TOPUP.OFFICE_PHONE, TBL_TOPUP.RESIDENTIAL_PHONE, TBL_TOPUP.DATE_VISIT_PLAN
                ,ISNULL(B.MS_STD_NAME,'') AS PROSPECT_TYPE, ISNULL(C.MS_STD_NAME,'') AS DEBTOR_TYPE
                ,ISNULL(D.CHANNEL_NAME,'') AS CHANNELING, ISNULL(E.MS_STD_NAME,'') AS EKSTERNAL
                ,ISNULL(B.MS_STD_ID,'') AS PROSPECT_TYPE_ID, ISNULL(C.MS_STD_ID,'') AS DEBTOR_TYPE_ID
                ,ISNULL(D.CHANNEL_ID,'') AS CHANNEL_ID, ISNULL(E.MS_STD_ID,'') AS EXTERNAL_ID, REFERENSI
                FROM 
                TBL_TOPUP_DOWNLOAD (NOLOCK) 
                INNER JOIN TBL_TOPUP (NOLOCK) ON TBL_TOPUP_DOWNLOAD.CIF_NUMBER = TBL_TOPUP.CIF_NUMBER AND TBL_TOPUP_DOWNLOAD.LOAN_NUMBER = TBL_TOPUP.LOAN_NUMBER 
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE MS_STD_GRP_ID = 5) AS B ON TBL_TOPUP.PROSPECT_TYPE = B.MS_STD_ID
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE MS_STD_GRP_ID = 9) AS C ON TBL_TOPUP.DEBTOR_TYPE = C.MS_STD_ID
                LEFT JOIN (SELECT CHANNEL_ID,CHANNEL_NAME FROM TBL_MS_CHANNEL (NOLOCK)) AS D ON TBL_TOPUP.CHANNELING = D.CHANNEL_ID
                LEFT JOIN (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE MS_STD_GRP_ID = 15) AS E ON TBL_TOPUP.[EKSTERNAL] = E.MS_STD_ID
                WHERE (TBL_TOPUP.UNIT_CODE = @UNIT_CODE) AND (TBL_TOPUP.IS_DOWNLOADED = '0') AND (TBL_TOPUP.IS_ASSIGNED = '0') ");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public int GetCountTopup(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT COUNT(1)
		        FROM TBL_TOPUP_DOWNLOAD (NOLOCK) 
                INNER JOIN TBL_TOPUP (NOLOCK) ON TBL_TOPUP_DOWNLOAD.CIF_NUMBER = TBL_TOPUP.CIF_NUMBER AND TBL_TOPUP_DOWNLOAD.LOAN_NUMBER = TBL_TOPUP.LOAN_NUMBER 
                WHERE (TBL_TOPUP.UNIT_CODE = @UNIT_CODE) AND (TBL_TOPUP.IS_DOWNLOADED = '0') AND (TBL_TOPUP.IS_ASSIGNED = '0')");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                object result = db.ExecuteScalar(cmd);

                if (result != null) return Convert.ToInt32(result);
            }
            return 0;
        }

        [WebMethod]
        public int GetCountRefferal(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT COUNT(1) FROM TBL_PROSPECT_REFFERAL (NOLOCK) 
		            WHERE RTRIM(LTRIM(UNIT_CODE_DEST)) = @UNIT_CODE AND IS_ASSIGNED = '0' AND IS_DOWNLOADED = '0'");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                object result = db.ExecuteScalar(cmd);

                if (result != null) return Convert.ToInt32(result);
            }
            return 0;
        }

        [WebMethod]
        public byte[] GetRefferalUM(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT REFFERAL_ID, FULL_NAME, JENIS_USAHA, ISNULL(A.MS_STD_NAME,'') AS JENIS_USAHA_DESC, 
                    OFFICE_PHONE, ISNULL(RENCANA_PLAFON,0) AS RENCANA_PLAFON, ADDRESS_BUSINESS,
                    UNIT_CODE_SOURCE, UNIT_CODE_DEST, AO_CODE_ORIG 
                FROM TBL_PROSPECT_REFFERAL (NOLOCK) LEFT JOIN
                    (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE IS_ACTIVE = '1' AND MS_STD_GRP_ID = 14) AS A
                ON TBL_PROSPECT_REFFERAL.JENIS_USAHA = A.MS_STD_ID
                WHERE RTRIM(LTRIM(TBL_PROSPECT_REFFERAL.UNIT_CODE_DEST)) = @UNIT_CODE 
                    AND TBL_PROSPECT_REFFERAL.IS_ASSIGNED = '0' AND (TBL_PROSPECT_REFFERAL.IS_DOWNLOADED = '0') ");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetRefferalSO(string nip, string pass, string unit, string AoCode)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT REFFERAL_ID, FULL_NAME, JENIS_USAHA, ISNULL(A.MS_STD_NAME,'') AS JENIS_USAHA_DESC, 
                    OFFICE_PHONE, ISNULL(RENCANA_PLAFON,0) AS RENCANA_PLAFON, ADDRESS_BUSINESS,
                    UNIT_CODE_SOURCE, UNIT_CODE_DEST, AO_CODE_ORIG, ASSIGNED_TO, ASSIGNED_DATE
                FROM TBL_PROSPECT_REFFERAL (NOLOCK) LEFT JOIN
                    (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE IS_ACTIVE = '1' AND MS_STD_GRP_ID = 14) AS A
                ON TBL_PROSPECT_REFFERAL.JENIS_USAHA = A.MS_STD_ID
                WHERE RTRIM(LTRIM(TBL_PROSPECT_REFFERAL.UNIT_CODE_DEST)) = @UNIT_CODE 
                    AND TBL_PROSPECT_REFFERAL.IS_ASSIGNED = '1' 
                    AND (TBL_PROSPECT_REFFERAL.IS_DOWNLOADED = '0') 
                    AND (LTRIM(RTRIM(ASSIGNED_TO)) = @AO_CODE) ");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);

                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetProspectAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PURGING_PERIOD INT, @PROCESS_DATE DATETIME
                SELECT TOP 1 @PURGING_PERIOD = PURGING_PERIOD, @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC

                SELECT [PROSPECT_ID],[PROSPECT_NO],[UNIT_CODE],[SOURCE_CODE],[AO_CODE]
                      ,[FULL_NAME],[KTP_NAME],[KTP_NO],[DATE_OF_BIRTH],[PLACE_OF_BIRTH]
                      ,[MOTHER_NAME],[MALE_FEMALE],[MARITAL_STATUS],[SPOUSE_NAME],[SPOUSE_DATE_OF_BIRTH]
                      ,[NPWP_NO],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS],[KELURAHAN_BUSINESS],[ZIP_CODE_BUSINESS]
                      ,[ADDRESS_BUSINESS],[RESIDENTIAL_STATUS],[RESIDENTIAL_PHONE],[MOBILE_PHONE],[OFFICE_STATUS]
                      ,[OFFICE_PHONE],[YEAR_OF_STAY],[JML_TANGGUNGAN],[JENIS_USAHA],[LAMA_USAHA]
                      ,[OMSET_PER_HARI],[ADA_REK_BANK],[RENCANA_PLAFON],[SALES_CHANNEL],[PROSPECT_TYPE]
                      ,[FUNDING_LENDING],[RESTRUCT_COMPONENT],[PROGRESS_STATUS],[CYCLE_STATUS],[INSTALLMENT]
                      ,[FAP_DATE],[NO_OF_CALLS],[IS_INTERESTED],[NOT_INTERESTED_REASON],[NOT_INTERESTED_NOTE]
                      ,[KET_KENDALA],[SCM_NOTE],[PRODUCT_TYPE],[CIF_NUMBER],[KARAKTER]
                      ,[REPUTASI_DEBITUR],[RIWAYAT_DSP],[TAKSIRAN_PLAFON],[TAKSIRAN_USAHA],[IS_DOCUMENT_COMPLETED]
                      ,[UM_DECISION],[UM_DECISION_NOTE],[UM_DECISION_DATE],[LOAN_NUMBER],[PRODUCT_CODE]
                      ,[CURRENT_OS],[CURRENT_INSTALLMENT],[OFFER_PLAFON],[OFFER_INSTALLMENT],[OFFER_INTEREST]
                      ,[OFFER_TENOR],[OFFER_FRESH_MONEY],[PRIORITY],[PIPELINE_START_TIME],[PIPELINE_FINISH_TIME]
                      ,[PROSPECT_START_TIME],[PROSPECT_FINISH_TIME],[BI_GOOD_UNTIL],[IS_BI_CHECKING],[IS_OFFLINE_ENTRY]
                      ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[IS_BY_UPLOAD]
                      ,[UPLOAD_BY],[UPLOAD_DATE],[IS_DELETED],[DEBTOR_TYPE],[GU_FUP]
                      ,[GU_CLOSE_DEAL],[IS_DEDUP],[CO_ASSIGNED],[CHANNELING],[OMZET_1]
                      ,[OMZET_2],[OMZET_3],[OMZET_4],[OMZET_5],[OMZET_6]
                      ,[OMZET_7],[OMZET_8],[OMZET_9],[OMZET_10],[OMZET_11]
                      ,[OMZET_12],[OMZET_AVERAGE],[IS_PIPELINE_KP],[EKSTERNAL],[REFERENSI]
                      ,[RES_1],[RES_2],[RES_3],[RES_4],[RES_5]
                      ,[RES_6],[RES_7],[RES_8],[RES_9],[RES_10]
                      ,[RES_11],[RES_12],[RES_13],[RES_14],[RES_15]
                      ,[RES_16],[RES_17],[RES_18],[RES_19],[RES_20]
                      ,[RES_21],[RES_22],[RES_23],[RES_24],[RES_25]
                      ,[RES_26],[RES_27],[RES_28],[RES_29],[RES_30]
                      ,[RES_31],[RES_32],[RES_33],[RES_34],[RES_35]
                      ,[RES_36],[RES_37],[RES_38],[RES_39],[RES_40]
                      ,[RES_41],[RES_42],[RES_43],[RES_44],[RES_45]
                      ,[RES_46],[RES_47],[RES_48],[RES_49],[RES_50]
                      ,[RES_51],[RES_52],[RES_53],[RES_54],[RES_55]
                      ,[RES_56],[RES_57],[RES_58],[RES_59],[RES_60]
                      ,[RES_61],[RES_62],[RES_63],[RES_64],[RES_65]
                      ,[RES_66],[RES_67],[RES_68],[RES_69],[RES_70]
                      ,[RES_71],[RES_72],[RES_73],[RES_74],[RES_75]
                      ,[RES_76],[RES_77],[RES_78],[RES_79],[RES_80]
                      ,[RES_81],[RES_82],[RES_83],[RES_84],[RES_85]
                      ,[RES_86],[RES_87],[RES_88],[RES_89],[RES_90]
                      ,[RES_91],[RES_92],[RES_93],[RES_94],[RES_95]
                      ,[RES_96],[RES_97],[RES_98],[RES_99],[RES_100]
                      ,[NIP],[OLD_NIP],[OLD_AO_CODE],[ALTERNATE_FOR]
                     FROM TBL_PROSPECT (NOLOCK) 
                    WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE 
                        AND UNIT_CODE = @UNIT_CODE
                        AND UPDATE_DATE >= DATEADD(mm,-1,DATEADD(dd,-1,@PROCESS_DATE))
                        --AND UPDATE_DATE >= DATEADD(mm,@PURGING_PERIOD * -1,DATEADD(dd,-1,GETDATE()))");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPVisitAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PURGING_PERIOD INT, @PROCESS_DATE DATETIME
                SELECT TOP 1 @PURGING_PERIOD = PURGING_PERIOD, @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC

                SELECT [PROSPECT_ID],[VISIT_NO],[OBJECTIVE_OF_VISIT],[DATE_VISIT_PLAN],[DATE_VISIT_ACTUAL]
                      ,[JOINT_FIELD_WORK],[RESULT_OF_VISIT],[REMARK],[ENTRY_BY],[ENTRY_DATE]
                      ,[UPDATE_BY],[UPDATE_DATE],[UPLOAD_BY],[UPLOAD_DATE] 
                FROM TBL_PROSPECT_VISIT (NOLOCK) WHERE PROSPECT_ID IN 
                (
                    SELECT PROSPECT_ID FROM TBL_PROSPECT (NOLOCK) 
                    WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE
                        AND UNIT_CODE = @UNIT_CODE
                        AND UPDATE_DATE >= DATEADD(mm,-1,DATEADD(dd,-1,@PROCESS_DATE))
                        --AND UPDATE_DATE >= DATEADD(mm,@PURGING_PERIOD * -1,DATEADD(dd,-1,GETDATE()))
                )");

                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPAddressAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PURGING_PERIOD INT, @PROCESS_DATE DATETIME
                SELECT TOP 1 @PURGING_PERIOD = PURGING_PERIOD, @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC

                SELECT [PROSPECT_ID],[ADDRESS_TYPE],[KODYA_KAB],[KECAMATAN],[KELURAHAN]
                      ,[ZIP_CODE],[ADDRESS],[ADDRESS_RT],[ADDRESS_RW],[ENTRY_BY]
                      ,[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[UPLOAD_BY],[UPLOAD_DATE]
                FROM TBL_PROSPECT_ADDRESS (NOLOCK) WHERE PROSPECT_ID IN 
                (
                    SELECT PROSPECT_ID FROM TBL_PROSPECT (NOLOCK) 
                    WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE
                        AND UNIT_CODE = @UNIT_CODE
                        AND UPDATE_DATE >= DATEADD(mm,-1,DATEADD(dd,-1,@PROCESS_DATE))
                        --AND UPDATE_DATE >= DATEADD(mm,@PURGING_PERIOD * -1,DATEADD(dd,-1,GETDATE()))
                )");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPBankAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PURGING_PERIOD INT, @PROCESS_DATE DATETIME
                SELECT TOP 1 @PURGING_PERIOD = PURGING_PERIOD, @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC

                SELECT [PROSPECT_ID],[BANK_NAME],[SIMPAN_PINJAM],[PLAFON],[JANGKA_BULAN]
                    ,[MATURITY_DATE],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE]
                    ,[UPLOAD_BY],[UPLOAD_DATE]
                FROM TBL_PROSPECT_BANK_HISTORY (NOLOCK) WHERE PROSPECT_ID IN 
                (
                    SELECT PROSPECT_ID FROM TBL_PROSPECT (NOLOCK) 
                    WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE 
                        AND UNIT_CODE = @UNIT_CODE 
                        AND UPDATE_DATE >= DATEADD(mm,-1,DATEADD(dd,-1,@PROCESS_DATE))
                        --AND UPDATE_DATE >= DATEADD(mm,@PURGING_PERIOD * -1,DATEADD(dd,-1,GETDATE()))
                )");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPDocumentAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PURGING_PERIOD INT, @PROCESS_DATE DATETIME
                SELECT TOP 1 @PURGING_PERIOD = PURGING_PERIOD, @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC

                SELECT  [PROSPECT_ID],[DOCUMENT_ID],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY]
                    ,[UPDATE_DATE],[UPLOAD_BY],[UPLOAD_DATE] 
                FROM TBL_PROSPECT_DOCUMENTS (NOLOCK) WHERE PROSPECT_ID IN 
                (
                    SELECT PROSPECT_ID FROM TBL_PROSPECT (NOLOCK) 
                    WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE
                        AND UNIT_CODE = @UNIT_CODE
                        AND UPDATE_DATE >= DATEADD(mm,-1,DATEADD(dd,-1,@PROCESS_DATE))
                        --AND UPDATE_DATE >= DATEADD(mm,@PURGING_PERIOD * -1,DATEADD(dd,-1,GETDATE()))
                )");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPEmgContactAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PURGING_PERIOD INT, @PROCESS_DATE DATETIME
                SELECT TOP 1 @PURGING_PERIOD = PURGING_PERIOD, @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC

                SELECT [PROSPECT_ID],[EMG_CONTACT_ID],[EMG_CONTACT_NAME],[EMG_RELATIONSHIP],[EMG_PHONE_1]
                      ,[EMG_PHONE_2],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE]
                      ,[UPLOAD_BY],[UPLOAD_DATE] 
                FROM TBL_PROSPECT_EMG_CONTACT (NOLOCK) WHERE PROSPECT_ID IN 
                (
                    SELECT PROSPECT_ID FROM TBL_PROSPECT (NOLOCK) 
                    WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE 
                        AND UNIT_CODE = @UNIT_CODE 
                        AND UPDATE_DATE >= DATEADD(mm,-1,DATEADD(dd,-1,@PROCESS_DATE))
                        --AND UPDATE_DATE >= DATEADD(mm,@PURGING_PERIOD * -1,DATEADD(dd,-1,GETDATE()))
                )");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetHoliday(string nip, string pass)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                SELECT TOP 1 @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK)
                SELECT HOLIDAY_DATE, HOLIDAY_NAME FROM TBL_MS_HOLIDAY (NOLOCK) 
	            WHERE LTRIM(RTRIM(IS_ACTIVE)) = '1' AND YEAR(HOLIDAY_DATE) = YEAR(@PROCESS_DATE) AND
		        (CONVERT(VARCHAR(10),HOLIDAY_DATE,120) >= CONVERT(VARCHAR(10),@PROCESS_DATE,120)) ");
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetExternalMap(string nip, string pass)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT EXTERNAL_ID, CHANNEL_ID, IS_ACTIVE FROM TBL_MS_EXTERNAL_MAPPING (NOLOCK)");
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetProspectUpdate(string nip, string pass, string AoCode, string unit, DateTime update)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT TBL_PROSPECT.* 
                FROM TBL_PROSPECT_UPDATE (NOLOCK) 
	            INNER JOIN TBL_PROSPECT (NOLOCK) ON TBL_PROSPECT_UPDATE.PROSPECT_ID = TBL_PROSPECT.PROSPECT_ID
                WHERE TBL_PROSPECT.UNIT_CODE = @UNIT_CODE AND TBL_PROSPECT.AO_CODE = @AO_CODE"); //AND TBL_PROSPECT_UPDATE.UPDATE_DATE > @UPDATE_DATE");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, update);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);

                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public int DelProspectUpdate(string nip, string pass, string prospect_id)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect_id);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public DateTime GetServerDateTime()
        {
            DateTime processDate = DateTime.Now;
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC");
            object result = db.ExecuteScalar(cmd);

            if (result != null)
            {
                processDate = Convert.ToDateTime(result);
            }
            else processDate = DateTime.Now;

            return processDate;
        }

        [WebMethod]
        public DateTime GetProcessingDate()
        {
            DateTime processDate = DateTime.Now;
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC");
            object result = db.ExecuteScalar(cmd);

            if (result != null)
            {
                processDate = Convert.ToDateTime(result);
            }
            else processDate = DateTime.Now;
            return processDate;
        }

        [WebMethod]
        public string UPipelineOld(byte[] data, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Shuba.Model.PipelineInfo pi;
                Shuba.Business.Pipeline bi = new Shuba.Business.Pipeline();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        pi = new Shuba.Model.PipelineInfo();

                        pi.ROW_ID = Convert.ToString(dr["ROW_ID"]);
                        pi.UNIT_CODE = Convert.ToString(dr["UNIT_CODE"]);
                        pi.FULL_NAME = Convert.ToString(dr["FULL_NAME"]);
                        pi.JENIS_USAHA = Convert.ToString(dr["JENIS_USAHA"]);
                        pi.RENCANA_PLAFON = Convert.ToDouble(dr["RENCANA_PLAFON"]);
                        pi.MOBILE_PHONE = Convert.ToString(dr["MOBILE_PHONE"]);
                        pi.OFFICE_PHONE = Convert.ToString(dr["OFFICE_PHONE"]);
                        pi.RESIDENTIAL_PHONE = Convert.ToString(dr["RESIDENTIAL_PHONE"]);
                        pi.KODYA_KAB_BUSINESS = Convert.ToString(dr["KODYA_KAB_BUSINESS"]);
                        pi.KECAMATAN_BUSINESS = Convert.ToString(dr["KECAMATAN_BUSINESS"]);
                        pi.KELURAHAN_BUSINESS = Convert.ToString(dr["KELURAHAN_BUSINESS"]);
                        pi.ADDRESS_BUSINESS = Convert.ToString(dr["ADDRESS_BUSINESS"]);
                        pi.ZIP_CODE_BUSINESS = Convert.ToString(dr["ZIP_CODE_BUSINESS"]);
                        pi.DATE_VISIT_PLAN = DateTime.Now;
                        pi.PROSPECT_TYPE = Convert.ToString(dr["PROSPECT_TYPE"]);
                        pi.DEBTOR_TYPE = Convert.ToString(dr["DEBTOR_TYPE"]);
                        pi.CHANNELING = Convert.ToString(dr["CHANNELING"]);
                        pi.ENTRY_BY = Convert.ToString(dr["ENTRY_BY"]);
                        pi.ENTRY_DATE = Convert.ToDateTime(dr["ENTRY_DATE"]);
                        pi.UPDATE_BY = Convert.ToString(dr["UPDATE_BY"]);
                        pi.UPDATE_DATE = Convert.ToDateTime(dr["UPDATE_DATE"]);
                        pi.OMZET_1 = Convert.ToDouble(dr["OMZET_1"]);
                        pi.OMZET_2 = Convert.ToDouble(dr["OMZET_2"]);
                        pi.OMZET_3 = Convert.ToDouble(dr["OMZET_3"]);
                        pi.OMZET_4 = Convert.ToDouble(dr["OMZET_4"]);
                        pi.OMZET_5 = Convert.ToDouble(dr["OMZET_5"]);
                        pi.OMZET_6 = Convert.ToDouble(dr["OMZET_6"]);
                        pi.OMZET_7 = Convert.ToDouble(dr["OMZET_7"]);
                        pi.OMZET_8 = Convert.ToDouble(dr["OMZET_8"]);
                        pi.OMZET_9 = Convert.ToDouble(dr["OMZET_9"]);
                        pi.OMZET_10 = Convert.ToDouble(dr["OMZET_10"]);
                        pi.OMZET_11 = Convert.ToDouble(dr["OMZET_11"]);
                        pi.OMZET_12 = Convert.ToDouble(dr["OMZET_12"]);
                        pi.OMZET_AVERAGE = Convert.ToDouble(dr["OMZET_AVERAGE"]); ;

                        if (bi.Update(pi) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UPipelineOld : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public int UpdatePipelineAssignedTo(DateTime AssignedDate, string ROW_ID, string AO_CODE, string LoginUser, string LoginPass)
        {
            int l_result = 0;
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();
                DbConnection connection = db.CreateConnection();
                connection.Open();
                DbTransaction dbTran = connection.BeginTransaction();

                try
                {
                    DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_PIPELINE SET IS_ASSIGNED = '1', IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE, ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE ROW_ID=@ROW_ID;");

                    db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);
                    db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                    db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, AssignedDate);
                    db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, LoginUser);
                    db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, AssignedDate);

                    if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                    {
                        cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PIPELINE_DOWNLOAD WHERE ROW_ID=@ROW_ID");
                        db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);
                        db.ExecuteNonQuery(cmd, dbTran);

                        cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_PIPELINE (NOLOCK) WHERE ROW_ID=@ROW_ID");
                        db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);

                        DataSet ds = db.ExecuteDataSet(cmd, dbTran);
                        if (ValidateDataSetEmpty(ds))
                        {
                            DataRow dr = ds.Tables[0].Rows[0];

                            Shuba.Business.AppParam appParam = new Shuba.Business.AppParam();
                            Shuba.Model.AppParamInfo parameter = appParam.Get();
                            Shuba.Business.Branch branch = new Shuba.Business.Branch();
                            Shuba.Business.MsStd std = new Shuba.Business.MsStd();
                            List<Shuba.Model.MstStdInfo> stdInfo = std.GetList(dr["JENIS_USAHA"].ToString(), 14);

                            int guFup = Convert.ToInt32(parameter.GU_FUP);
                            DateTime processTime = parameter.PROCESS_DATE;
                            string AssignedTo = dr["ASSIGNED_TO"].ToString().Trim().Length < 5 ? dr["ASSIGNED_TO"].ToString().Trim().PadRight(5, 'X') : dr["ASSIGNED_TO"].ToString().Trim();
                            string SourceCode = branch.Get(Convert.ToString(dr["UNIT_CODE"])).SourceCode;
                            DateTime dateFollowUp = AddWorkDays(processTime, guFup, GetHoliday());
                            string jenisUsaha = "";
                            if (stdInfo.Count > 0)
                            {
                                jenisUsaha = stdInfo[0].MstID;
                            }

                            string address = dr["ADDRESS_BUSINESS"].ToString().Trim();
                            string prospect_id = dr["UNIT_CODE"].ToString().Trim() + AssignedTo + ProcessTime(processTime).ToString("yyMMddHHmmss");
                            if (prospect_id.Trim().Length < 21) prospect_id = prospect_id.Trim().PadRight(21, 'X');
                            if (prospect_id.Trim().Length > 21) prospect_id = prospect_id.Trim().Substring(0, 21);
                            if (address.Trim().Length > 255) address = address.Substring(0, 255);

                            Shuba.Model.ProspectInfo prospect = new Shuba.Model.ProspectInfo
                            {
                                PROSPECT_ID = prospect_id.Trim().ToUpper(),
                                PROSPECT_NO = "n/a",
                                AO_CODE = dr["ASSIGNED_TO"].ToString(),
                                UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                SOURCE_CODE = SourceCode,
                                JENIS_USAHA = jenisUsaha,
                                FULL_NAME = dr["FULL_NAME"].ToString(),
                                RENCANA_PLAFON = dr["RENCANA_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["RENCANA_PLAFON"].ToString()),
                                MOBILE_PHONE = dr["MOBILE_PHONE"].ToString(),
                                OFFICE_PHONE = dr["OFFICE_PHONE"].ToString(),
                                RESIDENTIAL_PHONE = dr["RESIDENTIAL_PHONE"].ToString(),
                                KODYA_KAB_BUSINESS = dr["KODYA_KAB_BUSINESS"].ToString(),
                                KECAMATAN_BUSINESS = dr["KECAMATAN_BUSINESS"].ToString(),
                                KELURAHAN_BUSINESS = dr["KELURAHAN_BUSINESS"].ToString(),
                                ADDRESS_BUSINESS = address,
                                ZIP_CODE_BUSINESS = dr["ZIP_CODE_BUSINESS"].ToString(),
                                PROGRESS_STATUS = "NEW",
                                PROSPECT_TYPE = dr["PROSPECT_TYPE"].ToString(),
                                DEBTOR_TYPE = dr["DEBTOR_TYPE"].ToString(),
                                CHANNELING = dr["CHANNELING"].ToString(),
                                ENTRY_BY = LoginUser,
                                ENTRY_DATE = ProcessTime(processTime),
                                GU_FUP = dateFollowUp,
                                IS_DEDUP = "0",
                                OMZET_1 = dr["OMZET_1"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_1"].ToString()),
                                OMZET_2 = dr["OMZET_2"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_2"].ToString()),
                                OMZET_3 = dr["OMZET_3"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_3"].ToString()),
                                OMZET_4 = dr["OMZET_4"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_4"].ToString()),
                                OMZET_5 = dr["OMZET_5"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_5"].ToString()),
                                OMZET_6 = dr["OMZET_6"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_6"].ToString()),
                                OMZET_7 = dr["OMZET_7"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_7"].ToString()),
                                OMZET_8 = dr["OMZET_8"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_8"].ToString()),
                                OMZET_9 = dr["OMZET_9"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_9"].ToString()),
                                OMZET_10 = dr["OMZET_10"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_10"].ToString()),
                                OMZET_11 = dr["OMZET_11"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_11"].ToString()),
                                OMZET_12 = dr["OMZET_12"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_12"].ToString()),
                                OMZET_AVERAGE = dr["OMZET_AVERAGE"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_AVERAGE"].ToString()),
                                EKSTERNAL = dr["EKSTERNAL"].ToString(),
                                REFERENSI = dr["REFERENSI"].ToString(),
                                ALTERNATE_FOR = "",
                                FLAG_FREEZE = dr["FLAG_FREEZE"].ToString() == string.Empty ? Convert.ToInt16(0) : Convert.ToInt16(dr["FLAG_FREEZE"])
                            };

                            cmd = db.GetSqlStringCommand(@"INSERT INTO TBL_PROSPECT 
                                (PROSPECT_ID, PROSPECT_NO, UNIT_CODE, SOURCE_CODE, AO_CODE, 
                                 FULL_NAME, JENIS_USAHA, PROGRESS_STATUS, RENCANA_PLAFON, MOBILE_PHONE, 
                                 OFFICE_PHONE, RESIDENTIAL_PHONE, KODYA_KAB_BUSINESS, KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, 
                                 ADDRESS_BUSINESS, ZIP_CODE_BUSINESS, IS_DOCUMENT_COMPLETED, ENTRY_BY, ENTRY_DATE, 
                                 UPDATE_BY, UPDATE_DATE, PIPELINE_START_TIME, GU_FUP, IS_DEDUP,
                                 PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING,
                                 OMZET_1, OMZET_2, OMZET_3, OMZET_4, OMZET_5,
                                 OMZET_6, OMZET_7, OMZET_8, OMZET_9, OMZET_10,
                                 OMZET_11, OMZET_12, OMZET_AVERAGE, IS_PIPELINE_KP, EKSTERNAL, REFERENSI, NIP, ALTERNATE_FOR, KARAKTER, FLAG_FREEZE) 
                            VALUES (@PROSPECT_ID, @PROSPECT_NO, @UNIT_CODE, @SOURCE_CODE, @AO_CODE, 
                                 @FULL_NAME, @JENIS_USAHA, @PROGRESS_STATUS, @RENCANA_PLAFON, @MOBILE_PHONE, 
                                 @OFFICE_PHONE, @RESIDENTIAL_PHONE, @KODYA_KAB_BUSINESS, @KECAMATAN_BUSINESS, @KELURAHAN_BUSINESS, 
                                 @ADDRESS_BUSINESS, @ZIP_CODE_BUSINESS, @IS_DOCUMENT_COMPLETED, @ENTRY_BY, @ENTRY_DATE, 
                                 @UPDATE_BY, @UPDATE_DATE, @PIPELINE_START_TIME, @GU_FUP, @IS_DEDUP,
                                 @PROSPECT_TYPE, @DEBTOR_TYPE, @CHANNELING,
                                 @OMZET_1, @OMZET_2, @OMZET_3, @OMZET_4, @OMZET_5,
                                 @OMZET_6, @OMZET_7, @OMZET_8, @OMZET_9, @OMZET_10,
                                 @OMZET_11, @OMZET_12, @OMZET_AVERAGE, @IS_PIPELINE_KP, @EKSTERNAL, @REFERENSI, @NIP, @ALTERNATE_FOR, @KARAKTER, @FLAG_FREEZE)");

                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID.Trim().ToUpper());
                            db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, prospect.PROSPECT_NO);
                            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, prospect.UNIT_CODE);
                            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, prospect.SOURCE_CODE);
                            db.AddInParameter(cmd, "@AO_CODE", DbType.String, prospect.AO_CODE);
                            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, prospect.FULL_NAME);
                            db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, prospect.JENIS_USAHA);
                            db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, prospect.PROGRESS_STATUS);
                            db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, prospect.RENCANA_PLAFON);
                            db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, prospect.MOBILE_PHONE);
                            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, prospect.OFFICE_PHONE);
                            db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, prospect.RESIDENTIAL_PHONE);
                            db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, prospect.KODYA_KAB_BUSINESS);
                            db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, prospect.KECAMATAN_BUSINESS);
                            db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, prospect.KELURAHAN_BUSINESS);
                            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, prospect.ADDRESS_BUSINESS);
                            db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, prospect.ZIP_CODE_BUSINESS);
                            db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, "0");
                            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, prospect.GU_FUP);
                            db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, prospect.IS_DEDUP);
                            db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospect.PROSPECT_TYPE);
                            db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, prospect.DEBTOR_TYPE);
                            db.AddInParameter(cmd, "@CHANNELING", DbType.String, prospect.CHANNELING);
                            db.AddInParameter(cmd, "@OMZET_1", DbType.Double, prospect.OMZET_1);
                            db.AddInParameter(cmd, "@OMZET_2", DbType.Double, prospect.OMZET_2);
                            db.AddInParameter(cmd, "@OMZET_3", DbType.Double, prospect.OMZET_3);
                            db.AddInParameter(cmd, "@OMZET_4", DbType.Double, prospect.OMZET_4);
                            db.AddInParameter(cmd, "@OMZET_5", DbType.Double, prospect.OMZET_5);
                            db.AddInParameter(cmd, "@OMZET_6", DbType.Double, prospect.OMZET_6);
                            db.AddInParameter(cmd, "@OMZET_7", DbType.Double, prospect.OMZET_7);
                            db.AddInParameter(cmd, "@OMZET_8", DbType.Double, prospect.OMZET_8);
                            db.AddInParameter(cmd, "@OMZET_9", DbType.Double, prospect.OMZET_9);
                            db.AddInParameter(cmd, "@OMZET_10", DbType.Double, prospect.OMZET_10);
                            db.AddInParameter(cmd, "@OMZET_11", DbType.Double, prospect.OMZET_11);
                            db.AddInParameter(cmd, "@OMZET_12", DbType.Double, prospect.OMZET_12);
                            db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, prospect.OMZET_AVERAGE);
                            db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, "1");
                            db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, prospect.EKSTERNAL);
                            db.AddInParameter(cmd, "@REFERENSI", DbType.String, prospect.REFERENSI);
                            db.AddInParameter(cmd, "@NIP", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, prospect.ALTERNATE_FOR);
                            db.AddInParameter(cmd, "@KARAKTER", DbType.String, "3");
                            db.AddInParameter(cmd, "FLAG_FREEZE", DbType.Int16, prospect.FLAG_FREEZE);

                            if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                            {
                                cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID; INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");
                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID);
                                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, prospect.ENTRY_DATE);

                                l_result = db.ExecuteNonQuery(cmd, dbTran);
                            }
                        }
                    }
                    if (l_result > 0) dbTran.Commit();
                    else dbTran.Rollback();
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UpdatePipelineAssignedTo : " + ex.Message.ToString(), true);
                    dbTran.Rollback();
                }
                finally
                {
                    if (connection.State == ConnectionState.Open) connection.Close();
                }
            }
            return l_result;
        }

        [WebMethod]
        public string UTopupOld(byte[] data, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Shuba.Model.TopUpInfo pi;
                Shuba.Business.TopUp bi = new Shuba.Business.TopUp();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        pi = new Shuba.Model.TopUpInfo();

                        pi.CIF_NUMBER = Convert.ToString(dr["CIF_NUMBER"]);
                        pi.LOAN_NUMBER = Convert.ToString(dr["LOAN_NUMBER"]);
                        pi.UNIT_CODE = Convert.ToString(dr["UNIT_CODE"]);
                        pi.DEBITOR_NAME = Convert.ToString(dr["DEBITOR_NAME"]);
                        pi.PRODUCT_CODE = Convert.ToString(dr["PRODUCT_CODE"]);
                        pi.CURRENT_OS = Convert.ToDouble(dr["CURRENT_OS"]);
                        pi.CURRENT_INSTALLMENT = Convert.ToDouble(dr["CURRENT_INSTALLMENT"]);
                        pi.OFFER_PLAFON = Convert.ToDouble(dr["OFFER_PLAFON"]);
                        pi.OFFER_INSTALLMENT = Convert.ToDouble(dr["OFFER_INSTALLMENT"]);
                        pi.OFFER_INTEREST = Convert.ToString(dr["OFFER_INTEREST"]);
                        pi.OFFER_TENOR = Convert.ToInt16(dr["OFFER_TENOR"]);
                        pi.OFFER_FRESH_MONEY = Convert.ToDouble(dr["OFFER_FRESH_MONEY"]);
                        pi.PRIORITY = Convert.ToString(dr["PRIORITY"]);
                        pi.JENIS_USAHA = Convert.ToString(dr["JENIS_USAHA"]);
                        pi.KODYA_KAB_BUSINESS = Convert.ToString(dr["KODYA_KAB_BUSINESS"]);
                        pi.KECAMATAN_BUSINESS = Convert.ToString(dr["KECAMATAN_BUSINESS"]);
                        pi.KELURAHAN_BUSINESS = Convert.ToString(dr["KELURAHAN_BUSINESS"]);
                        pi.ZIP_CODE_BUSINESS = Convert.ToString(dr["ZIP_CODE_BUSINESS"]);
                        pi.ADDRESS_BUSINESS = Convert.ToString(dr["ADDRESS_BUSINESS"]);
                        pi.MOBILE_PHONE = Convert.ToString(dr["MOBILE_PHONE"]);
                        pi.OFFICE_PHONE = Convert.ToString(dr["OFFICE_PHONE"]);
                        pi.RESIDENTIAL_PHONE = Convert.ToString(dr["RESIDENTIAL_PHONE"]);
                        pi.DATE_VISIT_PLAN = DateTime.Now;
                        pi.ENTRY_BY = Convert.ToString(dr["ENTRY_BY"]);
                        pi.ENTRY_DATE = Convert.ToDateTime(dr["ENTRY_DATE"]);
                        pi.UPDATE_BY = Convert.ToString(dr["UPDATE_BY"]);
                        pi.UPDATE_DATE = Convert.ToDateTime(dr["UPDATE_DATE"]);
                        pi.PROSPECT_TYPE = Convert.ToString(dr["PROSPECT_TYPE"]);
                        pi.DEBTOR_TYPE = Convert.ToString(dr["DEBTOR_TYPE"]);
                        pi.CHANNELING = Convert.ToString(dr["CHANNELING"]);

                        if (bi.Update(pi) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UTopupOld : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string URefferalOld(byte[] data, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Shuba.Model.ProspectRefferalInfo pi;
                Shuba.Business.Prospect bi = new Shuba.Business.Prospect();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        pi = new Shuba.Model.ProspectRefferalInfo();

                        pi.REFFERAL_ID = Convert.ToString(dr["REFFERAL_ID"]);
                        pi.FULL_NAME = Convert.ToString(dr["FULL_NAME"]);
                        pi.JENIS_USAHA = Convert.ToString(dr["JENIS_USAHA"]);
                        pi.OFFICE_PHONE = Convert.ToString(dr["OFFICE_PHONE"]);
                        pi.RENCANA_PLAFON = Convert.ToDouble(dr["RENCANA_PLAFON"]);
                        pi.ADDRESS_BUSINESS = Convert.ToString(dr["ADDRESS_BUSINESS"]);
                        pi.UNIT_CODE_SOURCE = Convert.ToString(dr["UNIT_CODE_SOURCE"]);
                        pi.UNIT_CODE_DEST = Convert.ToString(dr["UNIT_CODE_DEST"]);
                        pi.AO_CODE_ORIG = Convert.ToString(dr["AO_CODE_ORIG"]);
                        pi.ENTRY_BY = Convert.ToString(dr["ENTRY_BY"]);
                        pi.ENTRY_DATE = Convert.ToDateTime(dr["ENTRY_DATE"]);
                        pi.UPDATE_BY = Convert.ToString(dr["UPDATE_BY"]);
                        pi.UPDATE_DATE = Convert.ToDateTime(dr["UPDATE_DATE"]);
                        pi.UPLOAD_BY = Convert.ToString(dr["UPLOAD_BY"]);
                        pi.UPLOAD_DATE = Convert.ToDateTime(dr["UPLOAD_DATE"]);

                        if (bi.SaveProspectRefferal2(pi) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 URefferalOld : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public int UpdateTopupAssignedTo(DateTime AssignedDate, string AO_CODE, string CIF_NUMBER, string LOAN_NUMBER, string LOGIN_USER, string LOGIN_PASSWD)
        {
            int l_result = 0;
            if (IsValidUser(LOGIN_USER, LOGIN_PASSWD))
            {
                Database db = CreateDatabase();
                DbConnection connection = db.CreateConnection();
                connection.Open();
                DbTransaction dbTran = connection.BeginTransaction();

                try
                {
                    DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_TOPUP SET IS_ASSIGNED = '1', IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE, ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                    db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                    db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                    db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                    db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, AssignedDate);
                    db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, LOGIN_USER);
                    db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, AssignedDate);

                    if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                    {
                        cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_TOPUP_DOWNLOAD WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                        db.ExecuteNonQuery(cmd, dbTran);

                        cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_TOPUP (NOLOCK) WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);

                        DataSet ds = db.ExecuteDataSet(cmd, dbTran);
                        if (ValidateDataSetEmpty(ds))
                        {
                            DataRow dr = ds.Tables[0].Rows[0];

                            Shuba.Business.AppParam appParam = new Shuba.Business.AppParam();
                            Shuba.Model.AppParamInfo parameter = appParam.Get();
                            Shuba.Business.Branch branch = new Shuba.Business.Branch();
                            Shuba.Business.MsStd std = new Shuba.Business.MsStd();
                            List<Shuba.Model.MstStdInfo> stdInfo = std.GetList(dr["JENIS_USAHA"].ToString(), 14);

                            int guFup = Convert.ToInt32(parameter.GU_FUP);
                            DateTime processTime = parameter.PROCESS_DATE;
                            string AssignedTo = dr["ASSIGNED_TO"].ToString().Trim().Length < 5 ? dr["ASSIGNED_TO"].ToString().Trim().PadRight(5, 'X') : dr["ASSIGNED_TO"].ToString().Trim();
                            string SourceCode = branch.Get(Convert.ToString(dr["UNIT_CODE"])).SourceCode;
                            DateTime dateFollowUp = AddWorkDays(processTime, guFup, GetHoliday());
                            string jenisUsaha = "";
                            if (stdInfo.Count > 0)
                            {
                                jenisUsaha = stdInfo[0].MstID;
                            }

                            string address = dr["ADDRESS_BUSINESS"].ToString().Trim();
                            string prospect_id = dr["UNIT_CODE"].ToString().Trim() + AssignedTo + ProcessTime(processTime).ToString("yyMMddHHmmss");
                            if (prospect_id.Trim().Length < 21) prospect_id = prospect_id.Trim().PadRight(21, 'X');
                            if (prospect_id.Trim().Length > 21) prospect_id = prospect_id.Trim().Substring(0, 21);
                            if (address.Trim().Length > 255) address = address.Substring(0, 255);

                            Shuba.Model.ProspectInfo prospect = new Shuba.Model.ProspectInfo
                            {
                                PROSPECT_ID = prospect_id.Trim().ToUpper(),
                                PROSPECT_NO = "n/a",
                                AO_CODE = dr["ASSIGNED_TO"].ToString().Trim(),
                                UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                SOURCE_CODE = SourceCode,
                                FULL_NAME = dr["DEBITOR_NAME"].ToString(),
                                CIF_NUMBER = dr["CIF_NUMBER"].ToString() == string.Empty ? 0 : int.Parse(dr["CIF_NUMBER"].ToString()),
                                LOAN_NUMBER = dr["LOAN_NUMBER"].ToString(),
                                PRODUCT_CODE = dr["PRODUCT_CODE"].ToString(),
                                CURRENT_OS = dr["CURRENT_OS"].ToString() == string.Empty ? 0 : float.Parse(dr["CURRENT_OS"].ToString()),
                                CURRENT_INSTALLMENT = dr["CURRENT_INSTALLMENT"].ToString() == string.Empty ? 0 : float.Parse(dr["CURRENT_INSTALLMENT"].ToString()),
                                OFFER_PLAFON = dr["OFFER_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_PLAFON"].ToString()),
                                RENCANA_PLAFON = dr["OFFER_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_PLAFON"].ToString()),
                                OFFER_INSTALLMENT = dr["OFFER_INSTALLMENT"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_INSTALLMENT"].ToString()),
                                OFFER_INTEREST = dr["OFFER_INTEREST"].ToString(),
                                OFFER_TENOR = dr["OFFER_TENOR"].ToString() == string.Empty ? Convert.ToInt16(0) : Int16.Parse(dr["OFFER_TENOR"].ToString()),
                                OFFER_FRESH_MONEY = dr["OFFER_FRESH_MONEY"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_FRESH_MONEY"].ToString()),
                                PRIORITY = dr["PRIORITY"].ToString(),
                                JENIS_USAHA = jenisUsaha,
                                KODYA_KAB_BUSINESS = dr["KODYA_KAB_BUSINESS"].ToString(),
                                KECAMATAN_BUSINESS = dr["KECAMATAN_BUSINESS"].ToString(),
                                KELURAHAN_BUSINESS = dr["KELURAHAN_BUSINESS"].ToString(),
                                ZIP_CODE_BUSINESS = dr["ZIP_CODE_BUSINESS"].ToString(),
                                ADDRESS_BUSINESS = address,
                                MOBILE_PHONE = dr["MOBILE_PHONE"].ToString(),
                                OFFICE_PHONE = dr["OFFICE_PHONE"].ToString(),
                                RESIDENTIAL_PHONE = dr["RESIDENTIAL_PHONE"].ToString(),
                                PROGRESS_STATUS = "NEW",
                                ENTRY_BY = LOGIN_USER,
                                ENTRY_DATE = ProcessTime(processTime),
                                GU_FUP = dateFollowUp,
                                PROSPECT_TYPE = dr["PROSPECT_TYPE"].ToString(),
                                DEBTOR_TYPE = dr["DEBTOR_TYPE"].ToString(),
                                CHANNELING = dr["CHANNELING"].ToString(),
                                EKSTERNAL = dr["EKSTERNAL"].ToString(),
                                REFERENSI = dr["REFERENSI"].ToString(),
                                ALTERNATE_FOR = "",
                                FLAG_FREEZE = dr["FLAG_FREEZE"].ToString() == string.Empty ? Convert.ToInt16(0) : Convert.ToInt16(dr["FLAG_FREEZE"])
                            };

                            cmd = db.GetSqlStringCommand(@"INSERT INTO TBL_PROSPECT ([PROSPECT_ID], [PROSPECT_NO], [UNIT_CODE], [SOURCE_CODE], [AO_CODE], [PROGRESS_STATUS], [CIF_NUMBER], 
                                                        [LOAN_NUMBER], [FULL_NAME], [PRODUCT_CODE], [CURRENT_OS], 
                                                        [CURRENT_INSTALLMENT], [RENCANA_PLAFON], [OFFER_PLAFON],
                                                        [OFFER_INSTALLMENT], [OFFER_INTEREST], [OFFER_TENOR], 
                                                        [OFFER_FRESH_MONEY], [PRIORITY], [KODYA_KAB_BUSINESS],
                                                        [KECAMATAN_BUSINESS], [KELURAHAN_BUSINESS], [ZIP_CODE_BUSINESS],
                                                        [ADDRESS_BUSINESS], [MOBILE_PHONE], [OFFICE_PHONE], [RESIDENTIAL_PHONE], [JENIS_USAHA],
                                                        [ENTRY_BY], [ENTRY_DATE], [UPDATE_BY], [UPDATE_DATE], [PIPELINE_START_TIME], [GU_FUP],
                                                        [PROSPECT_TYPE], [DEBTOR_TYPE], [CHANNELING], [EKSTERNAL], [REFERENSI], [NIP], [ALTERNATE_FOR], [KARAKTER], [FLAG_FREEZE])
                                                      VALUES (@PROSPECT_ID, @PROSPECT_NO, @UNIT_CODE, @SOURCE_CODE, @AO_CODE, @PROGRESS_STATUS, @CIF_NUMBER, 
                                                        @LOAN_NUMBER, @FULL_NAME, @PRODUCT_CODE, @CURRENT_OS, 
                                                        @CURRENT_INSTALLMENT, @RENCANA_PLAFON, @OFFER_PLAFON,
                                                        @OFFER_INSTALLMENT, @OFFER_INTEREST, @OFFER_TENOR, 
                                                        @OFFER_FRESH_MONEY, @PRIORITY, @KODYA_KAB_BUSINESS,
                                                        @KECAMATAN_BUSINESS, @KELURAHAN_BUSINESS, @ZIP_CODE_BUSINESS,
                                                        @ADDRESS_BUSINESS, @MOBILE_PHONE, @OFFICE_PHONE, @RESIDENTIAL_PHONE, @JENIS_USAHA,
                                                        @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @PIPELINE_START_TIME, @GU_FUP,
                                                        @PROSPECT_TYPE, @DEBTOR_TYPE, @CHANNELING, @EKSTERNAL, @REFERENSI, @NIP, @ALTERNATE_FOR, @KARAKTER, @FLAG_FREEZE)");
                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID.Trim().ToUpper());
                            db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, prospect.PROSPECT_NO);
                            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, prospect.UNIT_CODE);
                            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, prospect.SOURCE_CODE);
                            db.AddInParameter(cmd, "@AO_CODE", DbType.String, prospect.AO_CODE);
                            db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, prospect.PROGRESS_STATUS);
                            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, prospect.FULL_NAME);
                            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, prospect.CIF_NUMBER);
                            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, prospect.LOAN_NUMBER);
                            db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, prospect.PRODUCT_CODE);
                            db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, prospect.CURRENT_OS);
                            db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, prospect.CURRENT_INSTALLMENT);
                            db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, prospect.RENCANA_PLAFON);
                            db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, prospect.OFFER_PLAFON);
                            db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, prospect.OFFER_INSTALLMENT);
                            db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, prospect.OFFER_INTEREST);
                            db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, prospect.OFFER_TENOR);
                            db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, prospect.OFFER_FRESH_MONEY);
                            db.AddInParameter(cmd, "@PRIORITY", DbType.String, prospect.PRIORITY);
                            db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, prospect.KODYA_KAB_BUSINESS);
                            db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, prospect.KECAMATAN_BUSINESS);
                            db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, prospect.KELURAHAN_BUSINESS);
                            db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, prospect.ZIP_CODE_BUSINESS);
                            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, prospect.ADDRESS_BUSINESS);
                            db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, prospect.MOBILE_PHONE);
                            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, prospect.OFFICE_PHONE);
                            db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, prospect.RESIDENTIAL_PHONE);
                            db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, prospect.JENIS_USAHA);
                            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, prospect.GU_FUP);
                            db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospect.PROSPECT_TYPE);
                            db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, prospect.DEBTOR_TYPE);
                            db.AddInParameter(cmd, "@CHANNELING", DbType.String, prospect.CHANNELING);
                            db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, prospect.EKSTERNAL);
                            db.AddInParameter(cmd, "@REFERENSI", DbType.String, prospect.REFERENSI);
                            db.AddInParameter(cmd, "@NIP", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, prospect.ALTERNATE_FOR);
                            db.AddInParameter(cmd, "@KARAKTER", DbType.String, "2");
                            db.AddInParameter(cmd, "@FLAG_FREEZE", DbType.Int16, prospect.FLAG_FREEZE);

                            if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                            {
                                cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID; INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");
                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID);
                                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, prospect.ENTRY_DATE);

                                l_result = db.ExecuteNonQuery(cmd, dbTran);
                            }
                        }
                    }
                    if (l_result > 0) dbTran.Commit();
                    else dbTran.Rollback();
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UpdateTopupAssignedTo : " + ex.Message.ToString(), true);
                    dbTran.Rollback();
                }
                finally
                {
                    if (connection.State == ConnectionState.Open) connection.Close();
                }
            }
            return l_result;
        }

        private DateTime AddWorkDays(DateTime date, int workingDays, List<DateTime> Holidays)
        {
            int direction = workingDays < 0 ? -1 : 1;
            DateTime newDate = date.Date;
            while (workingDays != 0)
            {
                newDate = newDate.AddDays(direction);
                if (newDate.DayOfWeek != DayOfWeek.Saturday &&
                    newDate.DayOfWeek != DayOfWeek.Sunday &&
                    !Holidays.Contains(newDate))
                {
                    workingDays -= direction;
                }
            }
            return newDate;
        }

        private List<DateTime> GetHoliday()
        {
            List<DateTime> holiday = new List<DateTime>();
            try
            {
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"SELECT HOLIDAY_DATE FROM TBL_MS_HOLIDAY (NOLOCK) WHERE YEAR(HOLIDAY_DATE) = YEAR(GETDATE()) ORDER BY HOLIDAY_DATE");
                DataSet dt = db.ExecuteDataSet(cmd);

                if (ValidateDataSetEmpty(dt))
                {
                    foreach (DataRow item in dt.Tables[0].Rows)
                    {
                        holiday.Add(Convert.ToDateTime(item[0]));
                    }
                }
            }
            catch (Exception ex) 
            {
                CreateErrorLog("wsEnh2 GetHoliday : " + ex.Message.ToString(), true);
            }
            return holiday;
        }

        private bool ValidateDataSetEmpty(DataSet vDataSet)
        {
            if (vDataSet != null)
            {
                if (vDataSet.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
            }
            return false;
        }

        private DateTime ProcessTime(DateTime ProcesingDate)
        {
            return new DateTime(
                ProcesingDate.Year,
                ProcesingDate.Month,
                ProcesingDate.Day,
                DateTime.Now.Hour,
                DateTime.Now.Minute,
                DateTime.Now.Second,
                DateTime.Now.Millisecond,
                ProcesingDate.Kind);
        }

        [WebMethod]
        public string IsLogin(string NIP)
        {
            string status = "";
            long LoginPeriod;
            string AppCode = ConfigurationManager.AppSettings["app.code.spada"];
            
            Shuba.Business.User user = new Shuba.Business.User();
            Shuba.Model.UserInfo info = user.Get(NIP, AppCode);
            DateTime LastLoginDate = info.LoginDate;
            LoginPeriod = DateTime.Now.Subtract(LastLoginDate).Minutes;
            
            Int16 refreshTime = 5;
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 ISNULL(REFRESH_TIME, 5) REFRESH_TIME FROM TBL_SYS_PARAMS (NOLOCK)");
            object result = db.ExecuteScalar(cmd);
            if (result != null) refreshTime = Convert.ToInt16(result);

            if ((info.IsLogin == false) || (info.IsLogin == true && LoginPeriod >= refreshTime))
            {
                status = "";
            }
            else
            {
                status = String.Format("Tidak bisa Login, user ini sudah Login atau Logout dengan tidak benar\nLogin terakhir dari {0} pada {1:dd MMM yyyy hh:mm:ss tt}", NIP, LastLoginDate);
            }

            return status;
        }

        [WebMethod]
        public int UpdateIsMigrationUpload(string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK)

	            UPDATE TBL_MIGRATION SET
		            IS_MIGRATION_UPLOAD = '1', 
		            MIGRATION_UPLOAD_DATE = @PROCESS_DATE
	            WHERE AO_CODE=@AO_CODE
	            IF @@ROWCOUNT = 0
                INSERT INTO TBL_MIGRATION(AO_CODE, IS_MIGRATION_UPLOAD, MIGRATION_UPLOAD_DATE) 
                VALUES(@AO_CODE, '1', @PROCESS_DATE)");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public int UpdateIsMigrationDownload(string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK)

	            UPDATE TBL_MIGRATION SET
		            IS_MIGRATION_DOWNLOAD = '1', 
		            MIGRATION_DOWNLOAD_DATE = @PROCESS_DATE
	            WHERE AO_CODE=@AO_CODE
	            IF @@ROWCOUNT = 0
                BEGIN    
                    INSERT INTO TBL_MIGRATION(AO_CODE, IS_MIGRATION_DOWNLOAD, MIGRATION_DOWNLOAD_DATE) 
                    VALUES(@AO_CODE, '1', @PROCESS_DATE);
                END");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public int UpdateIsMigration(string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@" IF EXISTS (SELECT * FROM TBL_MIGRATION (NOLOCK) 
                WHERE IS_MIGRATION_DOWNLOAD = '1' 
                      AND IS_MIGRATION_UPLOAD = '1' 
                      AND AO_CODE = @AO_CODE)
                BEGIN 
                    UPDATE TBL_MIGRATION SET IS_MIGRATION_DOWNLOAD = '0', IS_MIGRATION_UPLOAD = '0' 
                    WHERE AO_CODE = @AO_CODE
                END");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public int UpdateMACAddress(string BRANCH_ID, string NIP, string MAC_ADDRESS, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @OLD_MAC_ADDRESS VARCHAR(50) 

                            SELECT @OLD_MAC_ADDRESS = MAC_ADDRESS 
                            FROM TBL_MS_BRANCH_NIP_SO (NOLOCK) WHERE BRANCH_ID = @BRANCH_ID AND NIP = @NIP

            	            UPDATE TBL_MS_BRANCH_NIP_SO SET
            		            MAC_ADDRESS = @MAC_ADDRESS, OLD_MAC_ADDRESS = @OLD_MAC_ADDRESS, IS_MIGRATION = '0'
                            WHERE BRANCH_ID = @BRANCH_ID AND NIP = @NIP AND IS_MIGRATION = '1';

                            UPDATE TBL_MS_BRANCH_NIP_SO SET
            		            MAC_ADDRESS = @MAC_ADDRESS
                            WHERE BRANCH_ID = @BRANCH_ID AND NIP = @NIP AND IS_MIGRATION = '0' AND MAC_ADDRESS = '' AND OLD_MAC_ADDRESS = '';");

                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
                db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, MAC_ADDRESS);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        bool IsValidUser(string LDAPUser, string LDAPPass)
        {
            return true;
        }
        
        bool IsLDAPUser(string LDAPUser, string LDAPPass)
        {
            if (basePage.ByPassAD())
                return true; //untuk testing;

            string LDAPServer = ConfigurationManager.AppSettings["ldap"];

            DirectoryEntry entry = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass);
            try
            {
                string EName = entry.Name; //Untuk Trap Error (LDAP tidak dapat) atau berhasil

                return true;
            }
            catch
            {
                return false;
            }
        }

        [WebMethod]
        public long CheckExpired(string LDAPUser, string LDAPPass)
        {
            long daysLeft = 0;
            try
            {
                long lastChanged = 0, maxDays = 0, maxPwdAge = 0;
                string LDAPServer = ConfigurationManager.AppSettings["ldap"];

                DirectoryEntry root = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass, AuthenticationTypes.Secure);
                DirectorySearcher searcher = new DirectorySearcher(root);

                searcher.Filter = "(&(objectClass=person)(SAMAccountName=" + LDAPUser + "))";
                searcher.PropertiesToLoad.Add("pwdlastset");

                SearchResult results = searcher.FindOne();

                if (results.Properties["pwdlastset"].Count > 0) lastChanged = (long)results.Properties["pwdlastset"][0];

                searcher = new DirectorySearcher(root);
                searcher.Filter = "maxPwdAge=*";
                searcher.PropertiesToLoad.Add("maxPwdAge");
                results = searcher.FindOne();

                if (results.Properties["maxPwdAge"].Count > 0) maxPwdAge = (Int64)results.Properties["maxPwdAge"][0];
                maxDays = maxPwdAge / -864000000000;

                daysLeft = maxDays - DateTime.Today.Subtract(Convert.ToDateTime(DateTime.FromFileTime((long)lastChanged).ToShortDateString())).Days;
            }
            catch (Exception ex) 
            {
                CreateErrorLog("wsEnh2 CheckExpired : " + ex.Message.ToString(), true);
            }
            return daysLeft;
        }

        [WebMethod]
        public string ResetPassword(string NIP, string oldPass, string newPass)
        {
            string res = "";
            string LDAPServer = ConfigurationManager.AppSettings["ldap"];
            DirectoryEntry uEntry = new DirectoryEntry(LDAPServer, NIP, oldPass, AuthenticationTypes.Secure);

            try
            {
                DirectoryEntry de = uEntry;
                DirectorySearcher search = new DirectorySearcher(de);
                search.Filter = "(SAMAccountName=" + NIP + ")";
                SearchResult result = search.FindOne();

                DirectoryEntry updateEntry = result.GetDirectoryEntry();

                updateEntry.Invoke("ChangePassword", new object[] { oldPass, newPass });
                updateEntry.CommitChanges();
                updateEntry.Close();

                search.Dispose();
            }
            catch (Exception ex) 
            {
                CreateErrorLog("wsEnh2 ResetPassword : " + ex.Message.ToString(), true);
                res = ex.InnerException.Message; 
            }
            return res;
        }

        [WebMethod]
        public bool IS_MIGRATION_DOWNLOAD(string AO_CODE, string LOGIN_USER, string LOGIN_PASSWD)
        {
            if (IsValidUser(LOGIN_USER, LOGIN_PASSWD))
            {
                object result;
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT IS_MIGRATION_DOWNLOAD FROM TBL_MIGRATION (NOLOCK) WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                result = db.ExecuteScalar(cmd);

                if (result == null) return false;
                else if (Convert.ToString(result).Trim() == "1") return true;
            }
            return false;
        }

        [WebMethod]
        public bool IS_MIGRATION_UPLOAD(string AO_CODE, string LOGIN_USER, string LOGIN_PASSWD)
        {
            if (IsValidUser(LOGIN_USER, LOGIN_PASSWD))
            {
                object result;
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT IS_MIGRATION_UPLOAD FROM TBL_MIGRATION (NOLOCK) WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                result = db.ExecuteScalar(cmd);

                if (result == null) return false;
                else if (Convert.ToString(result).Trim() == "1") return true;
            }
            return false;
        }

        [WebMethod]
        public int UReffealAssignedTo(DateTime AssignedDate, string REFFERAL_ID, string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_PROSPECT_REFFERAL SET IS_ASSIGNED = '1', ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE REFFERAL_ID=@REFFERAL_ID");

                db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, REFFERAL_ID);
                db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, AssignedDate);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public bool URefferalIsDownloaded(string AO_CODE, List<string> REFFERAL_ID, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();
                DbCommand cmd;
                for (int i = 0; i < REFFERAL_ID.Count; i++)
                {
                    cmd = db.GetSqlStringCommand(@"UPDATE TBL_PROSPECT_REFFERAL SET IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE WHERE ASSIGNED_TO = @ASSIGNED_TO AND REFFERAL_ID=@REFFERAL_ID");

                    db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, LoginUser);
                    db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, DateTime.Now);
                    db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                    db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, REFFERAL_ID[i]);
                    db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            return false;
        }
        
        [WebMethod]
        public string CheckLDAPSPADAUser(string LoginUser, string LoginPass, string Unit)
        {
            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();

            string UserUnitCode = "~";
            string SPADADBEncCode = Encryption.Decrypt(ConfigurationManager.AppSettings["enc.code.spada"]);
            string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

            if (IsLDAPUser(LoginUser, LoginPass))
            {
                object result;
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                                 SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) 

                                 SELECT TOP 1 BRANCH_ID FROM (SELECT LTRIM(RTRIM(BRANCH_ID)) BRANCH_ID, 1 AS ID 
	                             FROM V_USER (NOLOCK) 
	                             WHERE 
                                    (LTRIM(RTRIM(APP_ID)) LIKE @APP_ID) 
                                    AND (LTRIM(RTRIM(NIP)) LIKE @NIP) 
                                 UNION 
                                 SELECT	BRANCH_ID, 0 AS ID 
                                 FROM TBL_ALTERNATE_UM (NOLOCK)  
                                 WHERE  
                                    (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),EFF_DATE_FROM,120)) 
                                    AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),EFF_DATE_TO,120)) 
                                    AND BRANCH_ID = @BRANCH_ID AND (ALTERNATE_FOR = 'UM' OR ALTERNATE_FOR = 'LA') AND NIP = @NIP AND IS_ACTIVE = '1') X ORDER BY ID ");

                db.AddInParameter(cmd, "@APP_ID", DbType.String, SPADAAppCode);
                db.AddInParameter(cmd, "@NIP", DbType.String, LoginUser);
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, Unit);
                result = db.ExecuteScalar(cmd);

                string unit = result == null ? "na" : Convert.ToString(result);
                unit = unit == "" ? "na" : unit;
                UserUnitCode = unit + "," + SPADADBEncCode;

                return UserUnitCode;
            }
            else
            {
                return UserUnitCode + "," + SPADADBEncCode;
            }
        }
      
        [WebMethod]
        public byte[] GetProspecByDate(string table, string unit, string ao_code, DateTime start, DateTime end, string LoginUser, string LoginPass)
        {
            DataSet ds = new DataSet();
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("WS_GET_PROSPECT_BY_DATE");

                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@ID", DbType.String, table);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, ao_code);
                db.AddInParameter(cmd, "@DATE_START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@DATE_END", DbType.DateTime, end);

                ds = db.ExecuteDataSet(cmd);
            }
            return TransformIntoBytes(ds);
        }

        private byte[] TransformIntoBytes(System.Data.DataSet tables)
        {
            if (tables == null) return null;
            using (MemoryStream mem = new MemoryStream())
            {
                tables.WriteXml(mem, System.Data.XmlWriteMode.WriteSchema);
                byte[] result = mem.ToArray();
                if (result != null)
                {
                    if (result.Length > 0)
                    {
                        return result;
                    }
                }
            }
            return null;
        }

        private DataSet TransformIntoDataSet(byte[] value)
        {
            if (value == null) return null;
            System.Data.DataSet coreData = new DataSet("Core Data");
            coreData.ReadXml(new MemoryStream(value), System.Data.XmlReadMode.ReadSchema);
            return coreData;
        }

        [WebMethod]
        public string UploadProspect(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_TMP
                                SET 
                                    PROSPECT_NO = @PROSPECT_NO
                                    ,UNIT_CODE = @UNIT_CODE
                                    ,SOURCE_CODE = @SOURCE_CODE
                                    ,AO_CODE = @AO_CODE
                                    ,FULL_NAME = @FULL_NAME
                                    ,KTP_NAME = @KTP_NAME
                                    ,KTP_NO = @KTP_NO
                                    ,DATE_OF_BIRTH = @DATE_OF_BIRTH
                                    ,PLACE_OF_BIRTH = @PLACE_OF_BIRTH
                                    ,MOTHER_NAME = @MOTHER_NAME
                                    ,MALE_FEMALE = @MALE_FEMALE
                                    ,MARITAL_STATUS = @MARITAL_STATUS
                                    ,SPOUSE_NAME = @SPOUSE_NAME
                                    ,SPOUSE_DATE_OF_BIRTH = @SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO = @NPWP_NO
                                    ,KODYA_KAB_BUSINESS = @KODYA_KAB_BUSINESS
                                    ,KECAMATAN_BUSINESS = @KECAMATAN_BUSINESS
                                    ,KELURAHAN_BUSINESS = @KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS = @ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS = @ADDRESS_BUSINESS
                                    ,RESIDENTIAL_STATUS = @RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE = @RESIDENTIAL_PHONE
                                    ,MOBILE_PHONE = @MOBILE_PHONE
                                    ,OFFICE_STATUS = @OFFICE_STATUS
                                    ,OFFICE_PHONE = @OFFICE_PHONE
                                    ,YEAR_OF_STAY = @YEAR_OF_STAY
                                    ,JML_TANGGUNGAN = @JML_TANGGUNGAN
                                    ,JENIS_USAHA = @JENIS_USAHA
                                    ,LAMA_USAHA = @LAMA_USAHA
                                    ,OMSET_PER_HARI = @OMSET_PER_HARI
                                    ,ADA_REK_BANK = @ADA_REK_BANK
                                    ,RENCANA_PLAFON = @RENCANA_PLAFON
                                    ,SALES_CHANNEL = @SALES_CHANNEL
                                    ,PROSPECT_TYPE = @PROSPECT_TYPE
                                    ,FUNDING_LENDING = @FUNDING_LENDING
                                    ,RESTRUCT_COMPONENT = @RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS = @PROGRESS_STATUS
                                    ,CYCLE_STATUS = @CYCLE_STATUS
                                    ,INSTALLMENT = @INSTALLMENT
                                    ,FAP_DATE = @FAP_DATE
                                    ,NO_OF_CALLS = @NO_OF_CALLS
                                    ,IS_INTERESTED = @IS_INTERESTED
                                    ,NOT_INTERESTED_REASON = @NOT_INTERESTED_REASON
                                    ,NOT_INTERESTED_NOTE = @NOT_INTERESTED_NOTE
                                    ,KET_KENDALA = @KET_KENDALA
                                    ,SCM_NOTE = @SCM_NOTE
                                    ,PRODUCT_TYPE = @PRODUCT_TYPE
                                    ,CIF_NUMBER = @CIF_NUMBER
                                    ,KARAKTER = @KARAKTER
                                    ,REPUTASI_DEBITUR = @REPUTASI_DEBITUR
                                    ,RIWAYAT_DSP = @RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON = @TAKSIRAN_PLAFON
                                    ,TAKSIRAN_USAHA = @TAKSIRAN_USAHA
                                    ,IS_DOCUMENT_COMPLETED = @IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION = @UM_DECISION
                                    ,UM_DECISION_NOTE = @UM_DECISION_NOTE
                                    ,UM_DECISION_DATE = @UM_DECISION_DATE
                                    ,LOAN_NUMBER = @LOAN_NUMBER
                                    ,PRODUCT_CODE = @PRODUCT_CODE
                                    ,CURRENT_OS = @CURRENT_OS
                                    ,CURRENT_INSTALLMENT = @CURRENT_INSTALLMENT
                                    ,OFFER_PLAFON = @OFFER_PLAFON
                                    ,OFFER_INSTALLMENT = @OFFER_INSTALLMENT
                                    ,OFFER_INTEREST = @OFFER_INTEREST
                                    ,OFFER_TENOR = @OFFER_TENOR
                                    ,OFFER_FRESH_MONEY = @OFFER_FRESH_MONEY
                                    ,PRIORITY = @PRIORITY
                                    ,PIPELINE_START_TIME = @PIPELINE_START_TIME
                                    ,PIPELINE_FINISH_TIME = @PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME = @PROSPECT_START_TIME
                                    ,PROSPECT_FINISH_TIME = @PROSPECT_FINISH_TIME
                                    ,BI_GOOD_UNTIL = @BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING = @IS_BI_CHECKING
                                    ,IS_OFFLINE_ENTRY = @IS_OFFLINE_ENTRY
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,IS_BY_UPLOAD = @IS_BY_UPLOAD
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_DELETED = @IS_DELETED
                                    ,DEBTOR_TYPE = @DEBTOR_TYPE
                                    ,GU_FUP = @GU_FUP
                                    ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL
                                    ,IS_DEDUP = @IS_DEDUP
                                    ,CO_ASSIGNED = @CO_ASSIGNED
                                    ,CHANNELING = @CHANNELING
                                    ,OMZET_1 = @OMZET_1
                                    ,OMZET_2 = @OMZET_2
                                    ,OMZET_3 = @OMZET_3
                                    ,OMZET_4 = @OMZET_4
                                    ,OMZET_5 = @OMZET_5
                                    ,OMZET_6 = @OMZET_6
                                    ,OMZET_7 = @OMZET_7
                                    ,OMZET_8 = @OMZET_8
                                    ,OMZET_9 = @OMZET_9
                                    ,OMZET_10 = @OMZET_10
                                    ,OMZET_11 = @OMZET_11
                                    ,OMZET_12 = @OMZET_12
                                    ,OMZET_AVERAGE = @OMZET_AVERAGE
                                    ,IS_PIPELINE_KP = @IS_PIPELINE_KP
                                    ,[EKSTERNAL] = @EKSTERNAL
                                    ,REFERENSI = @REFERENSI
                                    ,RES_1 = @RES_1
                                    ,RES_2 = @RES_2
                                    ,RES_3 = @RES_3
                                    ,RES_4 = @RES_4
                                    ,RES_5 = @RES_5
                                    ,RES_6 = @RES_6
                                    ,RES_7 = @RES_7
                                    ,RES_8 = @RES_8
                                    ,RES_9 = @RES_9
                                    ,RES_10 = @RES_10
                                    ,RES_11 = @RES_11
                                    ,RES_12 = @RES_12
                                    ,RES_13 = @RES_13
                                    ,RES_14 = @RES_14
                                    ,RES_15 = @RES_15
                                    ,RES_16 = @RES_16
                                    ,RES_17 = @RES_17
                                    ,RES_18 = @RES_18
                                    ,RES_19 = @RES_19
                                    ,RES_20 = @RES_20
                                    ,RES_21 = @RES_21
                                    ,RES_22 = @RES_22
                                    ,RES_23 = @RES_23
                                    ,RES_24 = @RES_24
                                    ,RES_25 = @RES_25
                                    ,RES_26 = @RES_26
                                    ,RES_27 = @RES_27
                                    ,RES_28 = @RES_28
                                    ,RES_29 = @RES_29
                                    ,RES_30 = @RES_30
                                    ,RES_31 = @RES_31
                                    ,RES_32 = @RES_32
                                    ,RES_33 = @RES_33
                                    ,RES_34 = @RES_34
                                    ,RES_35 = @RES_35
                                    ,RES_36 = @RES_36
                                    ,RES_37 = @RES_37
                                    ,RES_38 = @RES_38
                                    ,RES_39 = @RES_39
                                    ,RES_40 = @RES_40
                                    ,RES_41 = @RES_41
                                    ,RES_42 = @RES_42
                                    ,RES_43 = @RES_43
                                    ,RES_44 = @RES_44
                                    ,RES_45 = @RES_45
                                    ,RES_46 = @RES_46
                                    ,RES_47 = @RES_47
                                    ,RES_48 = @RES_48
                                    ,RES_49 = @RES_49
                                    ,RES_50 = @RES_50
                                    ,RES_51 = @RES_51
                                    ,RES_52 = @RES_52
                                    ,RES_53 = @RES_53
                                    ,RES_54 = @RES_54
                                    ,RES_55 = @RES_55
                                    ,RES_56 = @RES_56
                                    ,RES_57 = @RES_57
                                    ,RES_58 = @RES_58
                                    ,RES_59 = @RES_59
                                    ,RES_60 = @RES_60
                                    ,RES_61 = @RES_61
                                    ,RES_62 = @RES_62
                                    ,RES_63 = @RES_63
                                    ,RES_64 = @RES_64
                                    ,RES_65 = @RES_65
                                    ,RES_66 = @RES_66
                                    ,RES_67 = @RES_67
                                    ,RES_68 = @RES_68
                                    ,RES_69 = @RES_69
                                    ,RES_70 = @RES_70
                                    ,RES_71 = @RES_71
                                    ,RES_72 = @RES_72
                                    ,RES_73 = @RES_73
                                    ,RES_74 = @RES_74
                                    ,RES_75 = @RES_75
                                    ,RES_76 = @RES_76
                                    ,RES_77 = @RES_77
                                    ,RES_78 = @RES_78
                                    ,RES_79 = @RES_79
                                    ,RES_80 = @RES_80
                                    ,RES_81 = @RES_81
                                    ,RES_82 = @RES_82
                                    ,RES_83 = @RES_83
                                    ,RES_84 = @RES_84
                                    ,RES_85 = @RES_85
                                    ,RES_86 = @RES_86
                                    ,RES_87 = @RES_87
                                    ,RES_88 = @RES_88
                                    ,RES_89 = @RES_89
                                    ,RES_90 = @RES_90
                                    ,RES_91 = @RES_91
                                    ,RES_92 = @RES_92
                                    ,RES_93 = @RES_93
                                    ,RES_94 = @RES_94
                                    ,RES_95 = @RES_95
                                    ,RES_96 = @RES_96
                                    ,RES_97 = @RES_97
                                    ,RES_98 = @RES_98
                                    ,RES_99 = @RES_99
                                    ,RES_100 = @RES_100
                                    ,NIP = @NIP
                                    ,OLD_NIP = @OLD_NIP
                                    ,OLD_AO_CODE = @OLD_AO_CODE
                                    ,ALTERNATE_FOR = @ALTERNATE_FOR
                                WHERE (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_TMP
                                (
                                    PROSPECT_ID
                                    ,PROSPECT_NO
                                    ,UNIT_CODE
                                    ,SOURCE_CODE
                                    ,AO_CODE
                                    ,FULL_NAME
                                    ,KTP_NAME
                                    ,KTP_NO
                                    ,DATE_OF_BIRTH
                                    ,PLACE_OF_BIRTH
                                    ,MOTHER_NAME
                                    ,MALE_FEMALE
                                    ,MARITAL_STATUS
                                    ,SPOUSE_NAME
                                    ,SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO
                                    ,KODYA_KAB_BUSINESS
                                    ,KECAMATAN_BUSINESS
                                    ,KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS
                                    ,RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE
                                    ,MOBILE_PHONE
                                    ,OFFICE_STATUS
                                    ,OFFICE_PHONE
                                    ,YEAR_OF_STAY
                                    ,JML_TANGGUNGAN
                                    ,JENIS_USAHA
                                    ,LAMA_USAHA
                                    ,OMSET_PER_HARI
                                    ,ADA_REK_BANK
                                    ,RENCANA_PLAFON
                                    ,SALES_CHANNEL
                                    ,PROSPECT_TYPE
                                    ,FUNDING_LENDING
                                    ,RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS
                                    ,CYCLE_STATUS
                                    ,INSTALLMENT
                                    ,FAP_DATE
                                    ,NO_OF_CALLS
                                    ,IS_INTERESTED
                                    ,NOT_INTERESTED_REASON
                                    ,NOT_INTERESTED_NOTE
                                    ,KET_KENDALA
                                    ,SCM_NOTE
                                    ,PRODUCT_TYPE
                                    ,CIF_NUMBER
                                    ,KARAKTER
                                    ,REPUTASI_DEBITUR
                                    ,RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON
                                    ,TAKSIRAN_USAHA
                                    ,IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION
                                    ,UM_DECISION_NOTE
                                    ,UM_DECISION_DATE
                                    ,LOAN_NUMBER
                                    ,PRODUCT_CODE
                                    ,CURRENT_OS
                                    ,CURRENT_INSTALLMENT
                                    ,OFFER_PLAFON
                                    ,OFFER_INSTALLMENT
                                    ,OFFER_INTEREST
                                    ,OFFER_TENOR
                                    ,OFFER_FRESH_MONEY
                                    ,PRIORITY
                                    ,PIPELINE_START_TIME
                                    ,PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME
                                    ,PROSPECT_FINISH_TIME
                                    ,BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING
                                    ,IS_OFFLINE_ENTRY
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,IS_BY_UPLOAD
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_DELETED
                                    ,DEBTOR_TYPE
                                    ,GU_FUP
                                    ,GU_CLOSE_DEAL
                                    ,IS_DEDUP
                                    ,CO_ASSIGNED
                                    ,CHANNELING
                                    ,OMZET_1
                                    ,OMZET_2
                                    ,OMZET_3
                                    ,OMZET_4
                                    ,OMZET_5
                                    ,OMZET_6
                                    ,OMZET_7
                                    ,OMZET_8
                                    ,OMZET_9
                                    ,OMZET_10
                                    ,OMZET_11
                                    ,OMZET_12
                                    ,OMZET_AVERAGE
                                    ,IS_PIPELINE_KP
                                    ,[EKSTERNAL]
                                    ,REFERENSI
                                    ,RES_1
                                    ,RES_2
                                    ,RES_3
                                    ,RES_4
                                    ,RES_5
                                    ,RES_6
                                    ,RES_7
                                    ,RES_8
                                    ,RES_9
                                    ,RES_10
                                    ,RES_11
                                    ,RES_12
                                    ,RES_13
                                    ,RES_14
                                    ,RES_15
                                    ,RES_16
                                    ,RES_17
                                    ,RES_18
                                    ,RES_19
                                    ,RES_20
                                    ,RES_21
                                    ,RES_22
                                    ,RES_23
                                    ,RES_24
                                    ,RES_25
                                    ,RES_26
                                    ,RES_27
                                    ,RES_28
                                    ,RES_29
                                    ,RES_30
                                    ,RES_31
                                    ,RES_32
                                    ,RES_33
                                    ,RES_34
                                    ,RES_35
                                    ,RES_36
                                    ,RES_37
                                    ,RES_38
                                    ,RES_39
                                    ,RES_40
                                    ,RES_41
                                    ,RES_42
                                    ,RES_43
                                    ,RES_44
                                    ,RES_45
                                    ,RES_46
                                    ,RES_47
                                    ,RES_48
                                    ,RES_49
                                    ,RES_50
                                    ,RES_51
                                    ,RES_52
                                    ,RES_53
                                    ,RES_54
                                    ,RES_55
                                    ,RES_56
                                    ,RES_57
                                    ,RES_58
                                    ,RES_59
                                    ,RES_60
                                    ,RES_61
                                    ,RES_62
                                    ,RES_63
                                    ,RES_64
                                    ,RES_65
                                    ,RES_66
                                    ,RES_67
                                    ,RES_68
                                    ,RES_69
                                    ,RES_70
                                    ,RES_71
                                    ,RES_72
                                    ,RES_73
                                    ,RES_74
                                    ,RES_75
                                    ,RES_76
                                    ,RES_77
                                    ,RES_78
                                    ,RES_79
                                    ,RES_80
                                    ,RES_81
                                    ,RES_82
                                    ,RES_83
                                    ,RES_84
                                    ,RES_85
                                    ,RES_86
                                    ,RES_87
                                    ,RES_88
                                    ,RES_89
                                    ,RES_90
                                    ,RES_91
                                    ,RES_92
                                    ,RES_93
                                    ,RES_94
                                    ,RES_95
                                    ,RES_96
                                    ,RES_97
                                    ,RES_98
                                    ,RES_99
                                    ,RES_100
                                    ,NIP 
                                    ,OLD_NIP
                                    ,OLD_AO_CODE
                                    ,ALTERNATE_FOR
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@PROSPECT_NO
                                    ,@UNIT_CODE
                                    ,@SOURCE_CODE
                                    ,@AO_CODE
                                    ,@FULL_NAME
                                    ,@KTP_NAME
                                    ,@KTP_NO
                                    ,@DATE_OF_BIRTH
                                    ,@PLACE_OF_BIRTH
                                    ,@MOTHER_NAME
                                    ,@MALE_FEMALE
                                    ,@MARITAL_STATUS
                                    ,@SPOUSE_NAME
                                    ,@SPOUSE_DATE_OF_BIRTH
                                    ,@NPWP_NO
                                    ,@KODYA_KAB_BUSINESS
                                    ,@KECAMATAN_BUSINESS
                                    ,@KELURAHAN_BUSINESS
                                    ,@ZIP_CODE_BUSINESS
                                    ,@ADDRESS_BUSINESS
                                    ,@RESIDENTIAL_STATUS
                                    ,@RESIDENTIAL_PHONE
                                    ,@MOBILE_PHONE
                                    ,@OFFICE_STATUS
                                    ,@OFFICE_PHONE
                                    ,@YEAR_OF_STAY
                                    ,@JML_TANGGUNGAN
                                    ,@JENIS_USAHA
                                    ,@LAMA_USAHA
                                    ,@OMSET_PER_HARI
                                    ,@ADA_REK_BANK
                                    ,@RENCANA_PLAFON
                                    ,@SALES_CHANNEL
                                    ,@PROSPECT_TYPE
                                    ,@FUNDING_LENDING
                                    ,@RESTRUCT_COMPONENT
                                    ,@PROGRESS_STATUS
                                    ,@CYCLE_STATUS
                                    ,@INSTALLMENT
                                    ,@FAP_DATE
                                    ,@NO_OF_CALLS
                                    ,@IS_INTERESTED
                                    ,@NOT_INTERESTED_REASON
                                    ,@NOT_INTERESTED_NOTE
                                    ,@KET_KENDALA
                                    ,@SCM_NOTE
                                    ,@PRODUCT_TYPE
                                    ,@CIF_NUMBER
                                    ,@KARAKTER
                                    ,@REPUTASI_DEBITUR
                                    ,@RIWAYAT_DSP
                                    ,@TAKSIRAN_PLAFON
                                    ,@TAKSIRAN_USAHA
                                    ,@IS_DOCUMENT_COMPLETED
                                    ,@UM_DECISION
                                    ,@UM_DECISION_NOTE
                                    ,@UM_DECISION_DATE
                                    ,@LOAN_NUMBER
                                    ,@PRODUCT_CODE
                                    ,@CURRENT_OS
                                    ,@CURRENT_INSTALLMENT
                                    ,@OFFER_PLAFON
                                    ,@OFFER_INSTALLMENT
                                    ,@OFFER_INTEREST
                                    ,@OFFER_TENOR
                                    ,@OFFER_FRESH_MONEY
                                    ,@PRIORITY
                                    ,@PIPELINE_START_TIME
                                    ,@PIPELINE_FINISH_TIME
                                    ,@PROSPECT_START_TIME
                                    ,@PROSPECT_FINISH_TIME
                                    ,@BI_GOOD_UNTIL
                                    ,@IS_BI_CHECKING
                                    ,@IS_OFFLINE_ENTRY
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@IS_BY_UPLOAD
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_DELETED
                                    ,@DEBTOR_TYPE
                                    ,@GU_FUP
                                    ,@GU_CLOSE_DEAL
                                    ,@IS_DEDUP
                                    ,@CO_ASSIGNED
                                    ,@CHANNELING
                                    ,@OMZET_1
                                    ,@OMZET_2
                                    ,@OMZET_3
                                    ,@OMZET_4
                                    ,@OMZET_5
                                    ,@OMZET_6
                                    ,@OMZET_7
                                    ,@OMZET_8
                                    ,@OMZET_9
                                    ,@OMZET_10
                                    ,@OMZET_11
                                    ,@OMZET_12
                                    ,@OMZET_AVERAGE
                                    ,@IS_PIPELINE_KP
                                    ,@EKSTERNAL
                                    ,@REFERENSI
                                    ,@RES_1
                                    ,@RES_2
                                    ,@RES_3
                                    ,@RES_4
                                    ,@RES_5
                                    ,@RES_6
                                    ,@RES_7
                                    ,@RES_8
                                    ,@RES_9
                                    ,@RES_10
                                    ,@RES_11
                                    ,@RES_12
                                    ,@RES_13
                                    ,@RES_14
                                    ,@RES_15
                                    ,@RES_16
                                    ,@RES_17
                                    ,@RES_18
                                    ,@RES_19
                                    ,@RES_20
                                    ,@RES_21
                                    ,@RES_22
                                    ,@RES_23
                                    ,@RES_24
                                    ,@RES_25
                                    ,@RES_26
                                    ,@RES_27
                                    ,@RES_28
                                    ,@RES_29
                                    ,@RES_30
                                    ,@RES_31
                                    ,@RES_32
                                    ,@RES_33
                                    ,@RES_34
                                    ,@RES_35
                                    ,@RES_36
                                    ,@RES_37
                                    ,@RES_38
                                    ,@RES_39
                                    ,@RES_40
                                    ,@RES_41
                                    ,@RES_42
                                    ,@RES_43
                                    ,@RES_44
                                    ,@RES_45
                                    ,@RES_46
                                    ,@RES_47
                                    ,@RES_48
                                    ,@RES_49
                                    ,@RES_50
                                    ,@RES_51
                                    ,@RES_52
                                    ,@RES_53
                                    ,@RES_54
                                    ,@RES_55
                                    ,@RES_56
                                    ,@RES_57
                                    ,@RES_58
                                    ,@RES_59
                                    ,@RES_60
                                    ,@RES_61
                                    ,@RES_62
                                    ,@RES_63
                                    ,@RES_64
                                    ,@RES_65
                                    ,@RES_66
                                    ,@RES_67
                                    ,@RES_68
                                    ,@RES_69
                                    ,@RES_70
                                    ,@RES_71
                                    ,@RES_72
                                    ,@RES_73
                                    ,@RES_74
                                    ,@RES_75
                                    ,@RES_76
                                    ,@RES_77
                                    ,@RES_78
                                    ,@RES_79
                                    ,@RES_80
                                    ,@RES_81
                                    ,@RES_82
                                    ,@RES_83
                                    ,@RES_84
                                    ,@RES_85
                                    ,@RES_86
                                    ,@RES_87
                                    ,@RES_88
                                    ,@RES_89
                                    ,@RES_90
                                    ,@RES_91
                                    ,@RES_92
                                    ,@RES_93
                                    ,@RES_94
                                    ,@RES_95
                                    ,@RES_96
                                    ,@RES_97
                                    ,@RES_98
                                    ,@RES_99
                                    ,@RES_100
                                    ,@NIP 
                                    ,@OLD_NIP
                                    ,@OLD_AO_CODE
                                    ,@ALTERNATE_FOR
                                );
                            ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                        db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, Convert.ToString(dr["SOURCE_CODE"]));
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                        db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                        db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                        db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@MOTHER_NAME", DbType.String, Convert.ToString(dr["MOTHER_NAME"]));
                        db.AddInParameter(cmd, "@MALE_FEMALE", DbType.String, Convert.ToString(dr["MALE_FEMALE"]));
                        db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String, Convert.ToString(dr["MARITAL_STATUS"]));
                        db.AddInParameter(cmd, "@SPOUSE_NAME", DbType.String, Convert.ToString(dr["SPOUSE_NAME"]));
                        db.AddInParameter(cmd, "@SPOUSE_DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["SPOUSE_DATE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@NPWP_NO", DbType.String, Convert.ToString(dr["NPWP_NO"]));
                        db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, Convert.ToString(dr["KODYA_KAB_BUSINESS"]));
                        db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, Convert.ToString(dr["KECAMATAN_BUSINESS"]));
                        db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, Convert.ToString(dr["KELURAHAN_BUSINESS"]));
                        db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, Convert.ToString(dr["ZIP_CODE_BUSINESS"]));
                        db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                        db.AddInParameter(cmd, "@RESIDENTIAL_STATUS", DbType.String, Convert.ToString(dr["RESIDENTIAL_STATUS"]));
                        db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                        db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                        db.AddInParameter(cmd, "@OFFICE_STATUS", DbType.String, Convert.ToString(dr["OFFICE_STATUS"]));
                        db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                        db.AddInParameter(cmd, "@YEAR_OF_STAY", DbType.Int16, Convert.ToInt16(dr["YEAR_OF_STAY"]));
                        db.AddInParameter(cmd, "@JML_TANGGUNGAN", DbType.Int16, Convert.ToInt16(dr["JML_TANGGUNGAN"]));
                        db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, Convert.ToString(dr["JENIS_USAHA"]));
                        db.AddInParameter(cmd, "@LAMA_USAHA", DbType.String, Convert.ToString(dr["LAMA_USAHA"]));
                        db.AddInParameter(cmd, "@OMSET_PER_HARI", DbType.Int32, Convert.ToInt32(dr["OMSET_PER_HARI"]));
                        db.AddInParameter(cmd, "@ADA_REK_BANK", DbType.String, Convert.ToString(dr["ADA_REK_BANK"]));
                        db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, Convert.ToDouble(dr["RENCANA_PLAFON"]));
                        db.AddInParameter(cmd, "@SALES_CHANNEL", DbType.String, Convert.ToString(dr["SALES_CHANNEL"]));
                        db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, Convert.ToString(dr["PROSPECT_TYPE"]));
                        db.AddInParameter(cmd, "@FUNDING_LENDING", DbType.String, Convert.ToString(dr["FUNDING_LENDING"]));
                        db.AddInParameter(cmd, "@RESTRUCT_COMPONENT", DbType.String, Convert.ToString(dr["RESTRUCT_COMPONENT"]));
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                        db.AddInParameter(cmd, "@CYCLE_STATUS", DbType.String, Convert.ToString(dr["CYCLE_STATUS"]));
                        db.AddInParameter(cmd, "@INSTALLMENT", DbType.Double, Convert.ToDouble(dr["INSTALLMENT"]));
                        db.AddInParameter(cmd, "@FAP_DATE", DbType.DateTime, Convert.ToDateTime(dr["FAP_DATE"]));
                        db.AddInParameter(cmd, "@NO_OF_CALLS", DbType.Int16, Convert.ToInt16(dr["NO_OF_CALLS"]));
                        db.AddInParameter(cmd, "@IS_INTERESTED", DbType.String, Convert.ToString(dr["IS_INTERESTED"]));
                        db.AddInParameter(cmd, "@NOT_INTERESTED_REASON", DbType.String, Convert.ToString(dr["NOT_INTERESTED_REASON"]));
                        db.AddInParameter(cmd, "@NOT_INTERESTED_NOTE", DbType.String, Convert.ToString(dr["NOT_INTERESTED_NOTE"]));
                        db.AddInParameter(cmd, "@KET_KENDALA", DbType.String, Convert.ToString(dr["KET_KENDALA"]));
                        db.AddInParameter(cmd, "@SCM_NOTE", DbType.String, Convert.ToString(dr["SCM_NOTE"]));
                        db.AddInParameter(cmd, "@PRODUCT_TYPE", DbType.Int16, Convert.ToInt16(dr["PRODUCT_TYPE"]));
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, Convert.ToInt32(dr["CIF_NUMBER"]));
                        db.AddInParameter(cmd, "@KARAKTER", DbType.String, Convert.ToString(dr["KARAKTER"]));
                        db.AddInParameter(cmd, "@REPUTASI_DEBITUR", DbType.String, Convert.ToString(dr["REPUTASI_DEBITUR"]));
                        db.AddInParameter(cmd, "@RIWAYAT_DSP", DbType.String, Convert.ToString(dr["RIWAYAT_DSP"]));
                        db.AddInParameter(cmd, "@TAKSIRAN_PLAFON", DbType.Double, Convert.ToDouble(dr["TAKSIRAN_PLAFON"]));
                        db.AddInParameter(cmd, "@TAKSIRAN_USAHA", DbType.String, Convert.ToString(dr["TAKSIRAN_USAHA"]));
                        db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, Convert.ToString(dr["IS_DOCUMENT_COMPLETED"]));
                        db.AddInParameter(cmd, "@UM_DECISION", DbType.String, Convert.ToString(dr["UM_DECISION"]));
                        db.AddInParameter(cmd, "@UM_DECISION_NOTE", DbType.String, Convert.ToString(dr["UM_DECISION_NOTE"]));
                        db.AddInParameter(cmd, "@UM_DECISION_DATE", DbType.DateTime, Convert.ToDateTime(dr["UM_DECISION_DATE"]));
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, Convert.ToString(dr["LOAN_NUMBER"]));
                        db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, Convert.ToString(dr["PRODUCT_CODE"]));
                        db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, Convert.ToDouble(dr["CURRENT_OS"]));
                        db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, Convert.ToDouble(dr["CURRENT_INSTALLMENT"]));
                        db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, Convert.ToDouble(dr["OFFER_PLAFON"]));
                        db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, Convert.ToDouble(dr["OFFER_INSTALLMENT"]));
                        db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, Convert.ToString(dr["OFFER_INTEREST"]));
                        db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, Convert.ToInt16(dr["OFFER_TENOR"]));
                        db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, Convert.ToDouble(dr["OFFER_FRESH_MONEY"]));
                        db.AddInParameter(cmd, "@PRIORITY", DbType.String, Convert.ToString(dr["PRIORITY"]));
                        db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, Convert.ToDateTime(dr["PIPELINE_START_TIME"]));
                        db.AddInParameter(cmd, "@PIPELINE_FINISH_TIME", DbType.DateTime, Convert.ToDateTime(dr["PIPELINE_FINISH_TIME"]));
                        db.AddInParameter(cmd, "@PROSPECT_START_TIME", DbType.DateTime, Convert.ToDateTime(dr["PROSPECT_START_TIME"]));
                        db.AddInParameter(cmd, "@PROSPECT_FINISH_TIME", DbType.DateTime, Convert.ToDateTime(dr["PROSPECT_FINISH_TIME"]));
                        db.AddInParameter(cmd, "@BI_GOOD_UNTIL", DbType.DateTime, Convert.ToDateTime(dr["BI_GOOD_UNTIL"]));
                        db.AddInParameter(cmd, "@IS_BI_CHECKING", DbType.String, Convert.ToString(dr["IS_BI_CHECKING"]));
                        db.AddInParameter(cmd, "@IS_OFFLINE_ENTRY", DbType.String, Convert.ToString(dr["IS_OFFLINE_ENTRY"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@IS_BY_UPLOAD", DbType.String, Convert.ToString(dr["IS_BY_UPLOAD"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));
                        db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, Convert.ToString(dr["DEBTOR_TYPE"]));
                        db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, Convert.ToDateTime(dr["GU_FUP"]));
                        db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.DateTime, Convert.ToDateTime(dr["GU_CLOSE_DEAL"]));
                        db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, Convert.ToString(dr["IS_DEDUP"]));
                        db.AddInParameter(cmd, "@CO_ASSIGNED", DbType.String, Convert.ToString(dr["CO_ASSIGNED"]));
                        db.AddInParameter(cmd, "@CHANNELING", DbType.String, Convert.ToString(dr["CHANNELING"]));
                        db.AddInParameter(cmd, "@OMZET_1", DbType.Double, Convert.ToDouble(dr["OMZET_1"]));
                        db.AddInParameter(cmd, "@OMZET_2", DbType.Double, Convert.ToDouble(dr["OMZET_2"]));
                        db.AddInParameter(cmd, "@OMZET_3", DbType.Double, Convert.ToDouble(dr["OMZET_3"]));
                        db.AddInParameter(cmd, "@OMZET_4", DbType.Double, Convert.ToDouble(dr["OMZET_4"]));
                        db.AddInParameter(cmd, "@OMZET_5", DbType.Double, Convert.ToDouble(dr["OMZET_5"]));
                        db.AddInParameter(cmd, "@OMZET_6", DbType.Double, Convert.ToDouble(dr["OMZET_6"]));
                        db.AddInParameter(cmd, "@OMZET_7", DbType.Double, Convert.ToDouble(dr["OMZET_7"]));
                        db.AddInParameter(cmd, "@OMZET_8", DbType.Double, Convert.ToDouble(dr["OMZET_8"]));
                        db.AddInParameter(cmd, "@OMZET_9", DbType.Double, Convert.ToDouble(dr["OMZET_9"]));
                        db.AddInParameter(cmd, "@OMZET_10", DbType.Double, Convert.ToDouble(dr["OMZET_10"]));
                        db.AddInParameter(cmd, "@OMZET_11", DbType.Double, Convert.ToDouble(dr["OMZET_11"]));
                        db.AddInParameter(cmd, "@OMZET_12", DbType.Double, Convert.ToDouble(dr["OMZET_12"]));
                        db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, Convert.ToDouble(dr["OMZET_AVERAGE"]));
                        db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, Convert.ToString(dr["IS_PIPELINE_KP"]));
                        db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, Convert.ToString(dr["EKSTERNAL"]));
                        db.AddInParameter(cmd, "@REFERENSI", DbType.String, Convert.ToString(dr["REFERENSI"]));
                        db.AddInParameter(cmd, "@RES_1", DbType.String, Convert.ToString(dr["RES_1"]));
                        db.AddInParameter(cmd, "@RES_2", DbType.String, Convert.ToString(dr["RES_2"]));
                        db.AddInParameter(cmd, "@RES_3", DbType.String, Convert.ToString(dr["RES_3"]));
                        db.AddInParameter(cmd, "@RES_4", DbType.String, Convert.ToString(dr["RES_4"]));
                        db.AddInParameter(cmd, "@RES_5", DbType.String, Convert.ToString(dr["RES_5"]));
                        db.AddInParameter(cmd, "@RES_6", DbType.String, Convert.ToString(dr["RES_6"]));
                        db.AddInParameter(cmd, "@RES_7", DbType.String, Convert.ToString(dr["RES_7"]));
                        db.AddInParameter(cmd, "@RES_8", DbType.String, Convert.ToString(dr["RES_8"]));
                        db.AddInParameter(cmd, "@RES_9", DbType.String, Convert.ToString(dr["RES_9"]));
                        db.AddInParameter(cmd, "@RES_10", DbType.String, Convert.ToString(dr["RES_10"]));
                        db.AddInParameter(cmd, "@RES_11", DbType.String, Convert.ToString(dr["RES_11"]));
                        db.AddInParameter(cmd, "@RES_12", DbType.String, Convert.ToString(dr["RES_12"]));
                        db.AddInParameter(cmd, "@RES_13", DbType.String, Convert.ToString(dr["RES_13"]));
                        db.AddInParameter(cmd, "@RES_14", DbType.String, Convert.ToString(dr["RES_14"]));
                        db.AddInParameter(cmd, "@RES_15", DbType.String, Convert.ToString(dr["RES_15"]));
                        db.AddInParameter(cmd, "@RES_16", DbType.String, Convert.ToString(dr["RES_16"]));
                        db.AddInParameter(cmd, "@RES_17", DbType.String, Convert.ToString(dr["RES_17"]));
                        db.AddInParameter(cmd, "@RES_18", DbType.String, Convert.ToString(dr["RES_18"]));
                        db.AddInParameter(cmd, "@RES_19", DbType.String, Convert.ToString(dr["RES_19"]));
                        db.AddInParameter(cmd, "@RES_20", DbType.String, Convert.ToString(dr["RES_20"]));
                        db.AddInParameter(cmd, "@RES_21", DbType.String, Convert.ToString(dr["RES_21"]));
                        db.AddInParameter(cmd, "@RES_22", DbType.String, Convert.ToString(dr["RES_22"]));
                        db.AddInParameter(cmd, "@RES_23", DbType.String, Convert.ToString(dr["RES_23"]));
                        db.AddInParameter(cmd, "@RES_24", DbType.String, Convert.ToString(dr["RES_24"]));
                        db.AddInParameter(cmd, "@RES_25", DbType.String, Convert.ToString(dr["RES_25"]));
                        db.AddInParameter(cmd, "@RES_26", DbType.DateTime, Convert.ToDateTime(dr["RES_26"]));
                        db.AddInParameter(cmd, "@RES_27", DbType.DateTime, Convert.ToDateTime(dr["RES_27"]));
                        db.AddInParameter(cmd, "@RES_28", DbType.DateTime, Convert.ToDateTime(dr["RES_28"]));
                        db.AddInParameter(cmd, "@RES_29", DbType.DateTime, Convert.ToDateTime(dr["RES_29"]));
                        db.AddInParameter(cmd, "@RES_30", DbType.DateTime, Convert.ToDateTime(dr["RES_30"]));
                        db.AddInParameter(cmd, "@RES_31", DbType.DateTime, Convert.ToDateTime(dr["RES_31"]));
                        db.AddInParameter(cmd, "@RES_32", DbType.DateTime, Convert.ToDateTime(dr["RES_32"]));
                        db.AddInParameter(cmd, "@RES_33", DbType.DateTime, Convert.ToDateTime(dr["RES_33"]));
                        db.AddInParameter(cmd, "@RES_34", DbType.DateTime, Convert.ToDateTime(dr["RES_34"]));
                        db.AddInParameter(cmd, "@RES_35", DbType.DateTime, Convert.ToDateTime(dr["RES_35"]));
                        db.AddInParameter(cmd, "@RES_36", DbType.DateTime, Convert.ToDateTime(dr["RES_36"]));
                        db.AddInParameter(cmd, "@RES_37", DbType.DateTime, Convert.ToDateTime(dr["RES_37"]));
                        db.AddInParameter(cmd, "@RES_38", DbType.DateTime, Convert.ToDateTime(dr["RES_38"]));
                        db.AddInParameter(cmd, "@RES_39", DbType.DateTime, Convert.ToDateTime(dr["RES_39"]));
                        db.AddInParameter(cmd, "@RES_40", DbType.DateTime, Convert.ToDateTime(dr["RES_40"]));
                        db.AddInParameter(cmd, "@RES_41", DbType.DateTime, Convert.ToDateTime(dr["RES_41"]));
                        db.AddInParameter(cmd, "@RES_42", DbType.DateTime, Convert.ToDateTime(dr["RES_42"]));
                        db.AddInParameter(cmd, "@RES_43", DbType.DateTime, Convert.ToDateTime(dr["RES_43"]));
                        db.AddInParameter(cmd, "@RES_44", DbType.DateTime, Convert.ToDateTime(dr["RES_44"]));
                        db.AddInParameter(cmd, "@RES_45", DbType.DateTime, Convert.ToDateTime(dr["RES_45"]));
                        db.AddInParameter(cmd, "@RES_46", DbType.DateTime, Convert.ToDateTime(dr["RES_46"]));
                        db.AddInParameter(cmd, "@RES_47", DbType.DateTime, Convert.ToDateTime(dr["RES_47"]));
                        db.AddInParameter(cmd, "@RES_48", DbType.DateTime, Convert.ToDateTime(dr["RES_48"]));
                        db.AddInParameter(cmd, "@RES_49", DbType.DateTime, Convert.ToDateTime(dr["RES_49"]));
                        db.AddInParameter(cmd, "@RES_50", DbType.DateTime, Convert.ToDateTime(dr["RES_50"]));
                        db.AddInParameter(cmd, "@RES_51", DbType.Double, Convert.ToDouble(dr["RES_51"]));
                        db.AddInParameter(cmd, "@RES_52", DbType.Double, Convert.ToDouble(dr["RES_52"]));
                        db.AddInParameter(cmd, "@RES_53", DbType.Double, Convert.ToDouble(dr["RES_53"]));
                        db.AddInParameter(cmd, "@RES_54", DbType.Double, Convert.ToDouble(dr["RES_54"]));
                        db.AddInParameter(cmd, "@RES_55", DbType.Double, Convert.ToDouble(dr["RES_55"]));
                        db.AddInParameter(cmd, "@RES_56", DbType.Double, Convert.ToDouble(dr["RES_56"]));
                        db.AddInParameter(cmd, "@RES_57", DbType.Double, Convert.ToDouble(dr["RES_57"]));
                        db.AddInParameter(cmd, "@RES_58", DbType.Double, Convert.ToDouble(dr["RES_58"]));
                        db.AddInParameter(cmd, "@RES_59", DbType.Double, Convert.ToDouble(dr["RES_59"]));
                        db.AddInParameter(cmd, "@RES_60", DbType.Double, Convert.ToDouble(dr["RES_60"]));
                        db.AddInParameter(cmd, "@RES_61", DbType.Double, Convert.ToDouble(dr["RES_61"]));
                        db.AddInParameter(cmd, "@RES_62", DbType.Double, Convert.ToDouble(dr["RES_62"]));
                        db.AddInParameter(cmd, "@RES_63", DbType.Double, Convert.ToDouble(dr["RES_63"]));
                        db.AddInParameter(cmd, "@RES_64", DbType.Double, Convert.ToDouble(dr["RES_64"]));
                        db.AddInParameter(cmd, "@RES_65", DbType.Double, Convert.ToDouble(dr["RES_65"]));
                        db.AddInParameter(cmd, "@RES_66", DbType.Double, Convert.ToDouble(dr["RES_66"]));
                        db.AddInParameter(cmd, "@RES_67", DbType.Double, Convert.ToDouble(dr["RES_67"]));
                        db.AddInParameter(cmd, "@RES_68", DbType.Double, Convert.ToDouble(dr["RES_68"]));
                        db.AddInParameter(cmd, "@RES_69", DbType.Double, Convert.ToDouble(dr["RES_69"]));
                        db.AddInParameter(cmd, "@RES_70", DbType.Double, Convert.ToDouble(dr["RES_70"]));
                        db.AddInParameter(cmd, "@RES_71", DbType.Double, Convert.ToDouble(dr["RES_71"]));
                        db.AddInParameter(cmd, "@RES_72", DbType.Double, Convert.ToDouble(dr["RES_72"]));
                        db.AddInParameter(cmd, "@RES_73", DbType.Double, Convert.ToDouble(dr["RES_73"]));
                        db.AddInParameter(cmd, "@RES_74", DbType.Double, Convert.ToDouble(dr["RES_74"]));
                        db.AddInParameter(cmd, "@RES_75", DbType.Double, Convert.ToDouble(dr["RES_75"]));
                        db.AddInParameter(cmd, "@RES_76", DbType.String, Convert.ToString(dr["RES_76"]));
                        db.AddInParameter(cmd, "@RES_77", DbType.String, Convert.ToString(dr["RES_77"]));
                        db.AddInParameter(cmd, "@RES_78", DbType.String, Convert.ToString(dr["RES_78"]));
                        db.AddInParameter(cmd, "@RES_79", DbType.String, Convert.ToString(dr["RES_79"]));
                        db.AddInParameter(cmd, "@RES_80", DbType.String, Convert.ToString(dr["RES_80"]));
                        db.AddInParameter(cmd, "@RES_81", DbType.String, Convert.ToString(dr["RES_81"]));
                        db.AddInParameter(cmd, "@RES_82", DbType.String, Convert.ToString(dr["RES_82"]));
                        db.AddInParameter(cmd, "@RES_83", DbType.String, Convert.ToString(dr["RES_83"]));
                        db.AddInParameter(cmd, "@RES_84", DbType.String, Convert.ToString(dr["RES_84"]));
                        db.AddInParameter(cmd, "@RES_85", DbType.String, Convert.ToString(dr["RES_85"]));
                        db.AddInParameter(cmd, "@RES_86", DbType.String, Convert.ToString(dr["RES_86"]));
                        db.AddInParameter(cmd, "@RES_87", DbType.String, Convert.ToString(dr["RES_87"]));
                        db.AddInParameter(cmd, "@RES_88", DbType.String, Convert.ToString(dr["RES_88"]));
                        db.AddInParameter(cmd, "@RES_89", DbType.String, Convert.ToString(dr["RES_89"]));
                        db.AddInParameter(cmd, "@RES_90", DbType.String, Convert.ToString(dr["RES_90"]));
                        db.AddInParameter(cmd, "@RES_91", DbType.String, Convert.ToString(dr["RES_91"]));
                        db.AddInParameter(cmd, "@RES_92", DbType.String, Convert.ToString(dr["RES_92"]));
                        db.AddInParameter(cmd, "@RES_93", DbType.String, Convert.ToString(dr["RES_93"]));
                        db.AddInParameter(cmd, "@RES_94", DbType.String, Convert.ToString(dr["RES_94"]));
                        db.AddInParameter(cmd, "@RES_95", DbType.String, Convert.ToString(dr["RES_95"]));
                        db.AddInParameter(cmd, "@RES_96", DbType.String, Convert.ToString(dr["RES_96"]));
                        db.AddInParameter(cmd, "@RES_97", DbType.String, Convert.ToString(dr["RES_97"]));
                        db.AddInParameter(cmd, "@RES_98", DbType.String, Convert.ToString(dr["RES_98"]));
                        db.AddInParameter(cmd, "@RES_99", DbType.String, Convert.ToString(dr["RES_99"]));
                        db.AddInParameter(cmd, "@RES_100", DbType.String, Convert.ToString(dr["RES_100"]));
                        db.AddInParameter(cmd, "@NIP", DbType.String, Convert.ToString(dr["NIP"]));
                        db.AddInParameter(cmd, "@OLD_NIP", DbType.String, Convert.ToString(dr["OLD_NIP"]));
                        db.AddInParameter(cmd, "@OLD_AO_CODE", DbType.String, Convert.ToString(dr["OLD_AO_CODE"]));
                        db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, Convert.ToString(dr["ALTERNATE_FOR"]));

                        if (db.ExecuteNonQuery(cmd) > 0)
                        {
                            result++;

                            if (Convert.ToString(dr["OLD_AO_CODE"]).Trim() != "")
                            {
                                cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID; INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");

                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                                db.ExecuteNonQuery(cmd);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadProspect : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectAddress(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_ADDRESS_TMP 
                                SET 
                                    KODYA_KAB = @KODYA_KAB
                                    ,KECAMATAN = @KECAMATAN
                                    ,KELURAHAN = @KELURAHAN
                                    ,ZIP_CODE = @ZIP_CODE
                                    ,ADDRESS = @ADDRESS
                                    ,ADDRESS_RT = @ADDRESS_RT
                                    ,ADDRESS_RW = @ADDRESS_RW
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (ADDRESS_TYPE = @ADDRESS_TYPE);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_ADDRESS_TMP
                                (
                                    PROSPECT_ID
                                    ,ADDRESS_TYPE
                                    ,KODYA_KAB
                                    ,KECAMATAN
                                    ,KELURAHAN
                                    ,ZIP_CODE
                                    ,ADDRESS
                                    ,ADDRESS_RT
                                    ,ADDRESS_RW
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@ADDRESS_TYPE
                                    ,@KODYA_KAB
                                    ,@KECAMATAN
                                    ,@KELURAHAN
                                    ,@ZIP_CODE
                                    ,@ADDRESS
                                    ,@ADDRESS_RT
                                    ,@ADDRESS_RW
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@ADDRESS_TYPE", DbType.String, Convert.ToString(dr["ADDRESS_TYPE"]));
                        db.AddInParameter(cmd, "@KODYA_KAB", DbType.String, Convert.ToString(dr["KODYA_KAB"]));
                        db.AddInParameter(cmd, "@KECAMATAN", DbType.String, Convert.ToString(dr["KECAMATAN"]));
                        db.AddInParameter(cmd, "@KELURAHAN", DbType.String, Convert.ToString(dr["KELURAHAN"]));
                        db.AddInParameter(cmd, "@ZIP_CODE", DbType.String, Convert.ToString(dr["ZIP_CODE"]));
                        db.AddInParameter(cmd, "@ADDRESS", DbType.String, Convert.ToString(dr["ADDRESS"]));
                        db.AddInParameter(cmd, "@ADDRESS_RT", DbType.String, Convert.ToString(dr["ADDRESS_RT"]));
                        db.AddInParameter(cmd, "@ADDRESS_RW", DbType.String, Convert.ToString(dr["ADDRESS_RW"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadProspectAddress : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectBank(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_BANK_HISTORY_TMP 
                                SET 
                                SIMPAN_PINJAM = @SIMPAN_PINJAM
                                ,PLAFON = @PLAFON
                                ,JANGKA_BULAN = @JANGKA_BULAN
                                ,MATURITY_DATE = @MATURITY_DATE
                                ,ENTRY_BY = @ENTRY_BY
                                ,ENTRY_DATE = @ENTRY_DATE
                                ,UPDATE_BY = @UPDATE_BY
                                ,UPDATE_DATE = @UPDATE_DATE
                                ,UPLOAD_BY = @UPLOAD_BY
                                ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (BANK_NAME = @BANK_NAME);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_BANK_HISTORY_TMP
                                (
                                    PROSPECT_ID
                                    ,BANK_NAME
                                    ,SIMPAN_PINJAM
                                    ,PLAFON
                                    ,JANGKA_BULAN
                                    ,MATURITY_DATE
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@BANK_NAME
                                    ,@SIMPAN_PINJAM
                                    ,@PLAFON
                                    ,@JANGKA_BULAN
                                    ,@MATURITY_DATE
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@BANK_NAME", DbType.String, Convert.ToString(dr["BANK_NAME"]));
                        db.AddInParameter(cmd, "@SIMPAN_PINJAM", DbType.String, Convert.ToString(dr["SIMPAN_PINJAM"]));
                        db.AddInParameter(cmd, "@PLAFON", DbType.Double, Convert.ToDouble(dr["PLAFON"]));
                        db.AddInParameter(cmd, "@JANGKA_BULAN", DbType.Int16, Convert.ToInt16(dr["JANGKA_BULAN"]));
                        db.AddInParameter(cmd, "@MATURITY_DATE", DbType.DateTime, Convert.ToDateTime(dr["MATURITY_DATE"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadProspectBank : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectDoc(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_DOCUMENTS_TMP 
                                SET 
                                    ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (DOCUMENT_ID = @DOCUMENT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_DOCUMENTS_TMP
                                (
                                    PROSPECT_ID
                                    ,DOCUMENT_ID
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@DOCUMENT_ID
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@DOCUMENT_ID", DbType.Int16, Convert.ToInt16(dr["DOCUMENT_ID"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadProspectDoc : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectEmgContact(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_EMG_CONTACT_TMP 
                                SET 
                                    EMG_CONTACT_NAME = @EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP = @EMG_RELATIONSHIP
                                    ,EMG_PHONE_1 = @EMG_PHONE_1
                                    ,EMG_PHONE_2 = @EMG_PHONE_2
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (EMG_CONTACT_ID = @EMG_CONTACT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_EMG_CONTACT_TMP
                                (
                                    PROSPECT_ID
                                    ,EMG_CONTACT_ID
                                    ,EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP
                                    ,EMG_PHONE_1
                                    ,EMG_PHONE_2
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@EMG_CONTACT_ID
                                    ,@EMG_CONTACT_NAME
                                    ,@EMG_RELATIONSHIP
                                    ,@EMG_PHONE_1
                                    ,@EMG_PHONE_2
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@EMG_CONTACT_ID", DbType.Byte, Convert.ToByte(dr["EMG_CONTACT_ID"]));
                        db.AddInParameter(cmd, "@EMG_CONTACT_NAME", DbType.String, Convert.ToString(dr["EMG_CONTACT_NAME"]));
                        db.AddInParameter(cmd, "@EMG_RELATIONSHIP", DbType.String, Convert.ToString(dr["EMG_RELATIONSHIP"]));
                        db.AddInParameter(cmd, "@EMG_PHONE_1", DbType.String, Convert.ToString(dr["EMG_PHONE_1"]));
                        db.AddInParameter(cmd, "@EMG_PHONE_2", DbType.String, Convert.ToString(dr["EMG_PHONE_2"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadProspectEmgContact : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectVisit(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Shuba.Model.ProspectVisitInfo pvi;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        pvi = new Shuba.Model.ProspectVisitInfo();

                        pvi.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        pvi.VISIT_NO = Convert.ToByte(theRow["VISIT_NO"]);
                        pvi.OBJECTIVE_OF_VISIT = theRow["OBJECTIVE_OF_VISIT"].ToString();
                        pvi.DATE_VISIT_PLAN = Convert.ToDateTime(theRow["DATE_VISIT_PLAN"]);
                        pvi.DATE_VISIT_ACTUAL = Convert.ToDateTime(theRow["DATE_VISIT_ACTUAL"]);
                        pvi.JOINT_FIELD_WORK = theRow["JOINT_FIELD_WORK"].ToString();
                        pvi.RESULT_OF_VISIT = theRow["RESULT_OF_VISIT"].ToString();
                        pvi.REMARK = theRow["REMARK"].ToString();
                        pvi.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        pvi.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        pvi.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        pvi.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        pvi.UPLOAD_BY = loginUser;
                        pvi.UPLOAD_DATE = uploadDate;

                        if (_Prospect.SaveProspectVisit2(pvi) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadProspectVisit : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadMaintenanceVisit(byte[] data, string loginUser, string loginPass, ref int res)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Shuba.Model.MaintenanceVisitInfo mvi;
                Shuba.Business.MaintenanceVisit _MaintenanceVisit = new Shuba.Business.MaintenanceVisit();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        mvi = new Shuba.Model.MaintenanceVisitInfo();

                        mvi.VISIT_ID = theRow["VISIT_ID"].ToString();
                        mvi.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        mvi.AO_CODE = theRow["AO_CODE"].ToString();
                        mvi.UNIT_CODE = theRow["UNIT_CODE"].ToString();
                        mvi.DATE_VISIT = Convert.ToDateTime(theRow["DATE_VISIT"]);
                        mvi.NO_ACCOUNT = theRow["NO_ACCOUNT"].ToString();
                        mvi.DEBTOR_NAME = theRow["DEBTOR_NAME"].ToString();
                        mvi.OBJECTIVE_OF_VISIT = theRow["OBJECTIVE_OF_VISIT"].ToString();
                        mvi.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        mvi.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        mvi.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        mvi.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        mvi.IS_DELETED = theRow["IS_DELETED"].ToString();

                        if (_MaintenanceVisit.Save(mvi) > 0) res++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadMaintenanceVisit : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadSoTargetPlan(byte[] data, string loginUser, string loginPass)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Shuba.Model.SOTargetPlanInfo soi;
                Shuba.Business.SOTargetPlan _SOTargetPlan = new Shuba.Business.SOTargetPlan();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        soi = new Shuba.Model.SOTargetPlanInfo();

                        soi.TARGET_YEAR = theRow["TARGET_YEAR"].ToString();
                        soi.TARGET_MONTH = theRow["TARGET_MONTH"].ToString();
                        soi.AO_CODE = theRow["AO_CODE"].ToString();
                        soi.UNIT_CODE = theRow["UNIT_CODE"].ToString();
                        soi.TARGET_AMOUNT = Convert.ToDouble(theRow["TARGET_AMOUNT"]);
                        soi.NOA = Convert.ToInt32(theRow["NOA"]);
                        soi.TARGET_AMOUNT_REAL = Convert.ToDouble(theRow["TARGET_AMOUNT_REAL"]);
                        soi.NOA_REAL = Convert.ToInt32(theRow["NOA_REAL"]);
                        soi.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        soi.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        soi.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        soi.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        soi.IS_DELETED = theRow["IS_DELETED"].ToString();

                        _SOTargetPlan.SaveSOTargetPlan(soi);
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadSoTargetPlan : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectRefferal(byte[] data, DateTime uploadDate, string loginUser, string loginPass)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Shuba.Model.ProspectRefferalInfo pri;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        pri = new Shuba.Model.ProspectRefferalInfo();

                        pri.REFFERAL_ID = theRow["REFFERAL_ID"].ToString();
                        pri.FULL_NAME = theRow["FULL_NAME"].ToString();
                        pri.JENIS_USAHA = theRow["JENIS_USAHA"].ToString();
                        pri.OFFICE_PHONE = theRow["OFFICE_PHONE"].ToString();
                        pri.RENCANA_PLAFON = Convert.ToDouble(theRow["RENCANA_PLAFON"]);
                        pri.ADDRESS_BUSINESS = theRow["ADDRESS_BUSINESS"].ToString();
                        pri.UNIT_CODE_SOURCE = theRow["UNIT_CODE_SOURCE"].ToString();
                        pri.UNIT_CODE_DEST = theRow["UNIT_CODE_DEST"].ToString();
                        pri.AO_CODE_ORIG = theRow["AO_CODE_ORIG"].ToString();
                        pri.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        pri.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        pri.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        pri.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        pri.UPLOAD_BY = loginUser; // theRow["UPLOAD_BY"].ToString();
                        pri.UPLOAD_DATE = uploadDate; //Convert.ToDateTime(theRow["UPLOAD_DATE"]);
                        pri.IS_DOWNLOADED = "0";
                        pri.DOWNLOAD_BY = "";// UploadBy;
                        pri.DOWNLOAD_DATE = Convert.ToDateTime("01-01-2000"); //UploadDate;

                        _Prospect.SaveProspectRefferal(pri);
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("wsEnh2 UploadProspectRefferal : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public int UploadUserZip(byte[] data, string loginUser, string loginPass)
        {
            int result = 0;
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);
                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_MS_USER_ZIP_TMP 
                                SET AO_CODE = @AO_CODE
                                    ,ZIP_CODE = @ZIP_CODE
                                    ,KODYA_KAB = @KODYA_KAB
                                    ,KECAMATAN = @KECAMATAN
                                    ,KELURAHAN = @KELURAHAN
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                WHERE (AO_CODE = @AO_CODE AND ZIP_CODE = @ZIP_CODE AND KODYA_KAB = @KODYA_KAB AND KECAMATAN = @KECAMATAN AND KELURAHAN = @KELURAHAN);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_MS_USER_ZIP_TMP 
                                (
                                    AO_CODE,
                                    ZIP_CODE,
                                    KODYA_KAB,
                                    KECAMATAN,
                                    KELURAHAN,
                                    UPDATE_BY,
                                    UPDATE_DATE
                                ) 
                                VALUES
                                (
                                    @AO_CODE,
                                    @ZIP_CODE,
                                    @KODYA_KAB,
                                    @KECAMATAN,
                                    @KELURAHAN,
                                    @UPDATE_BY,
                                    @UPDATE_DATE
                                );
                            ");
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(theRow["AO_CODE"]));
                        db.AddInParameter(cmd, "@ZIP_CODE", DbType.String, Convert.ToString(theRow["ZIP_CODE"]));
                        db.AddInParameter(cmd, "@KODYA_KAB", DbType.String, Convert.ToString(theRow["KODYA_KAB"]));
                        db.AddInParameter(cmd, "@KECAMATAN", DbType.String, Convert.ToString(theRow["KECAMATAN"]));
                        db.AddInParameter(cmd, "@KELURAHAN", DbType.String, Convert.ToString(theRow["KELURAHAN"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(theRow["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(theRow["UPDATE_DATE"]));

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch { return result; }
            }
            else return result;
            return result;
        }

        [WebMethod]
        public int DeleteUserZip(string ao_code, string loginUser, string loginPass)
        {
            int result = 0;
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = CreateDatabase();
                try
                {
                    DbCommand cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_MS_USER_ZIP_TMP WHERE AO_CODE = @AO_CODE;");
                    db.AddInParameter(cmd, "@AO_CODE", DbType.String, ao_code);

                    if (db.ExecuteNonQuery(cmd) > 0) result++;
                }
                catch { return result; }
            }
            else return result;
            return result;
        }

        [WebMethod]
        public byte[] GetUserZip(string ao_code, string nip, string pass, DateTime updateDate)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_USER_ZIP (NOLOCK) WHERE AO_CODE = @AO_CODE AND UPDATE_DATE > @UPDATE_DATE");
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, ao_code);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, updateDate);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));

                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetSoPlan(string nip, string pass, string unit_code, DateTime updateDate)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_SO_TARGET_PLAN (NOLOCK) WHERE UNIT_CODE = @UNIT_CODE AND UPDATE_DATE > @UPDATE_DATE");
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit_code);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, updateDate);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));

                return bite;
            }
            return null;
        }

        void CreateErrorLog(string text, bool isnewline)
        {
            try
            {
                string appPath = Server.MapPath("~/AppLog");
                if (appPath.Substring(appPath.Length - 1, 1) == "\\")
                {
                    appPath = appPath.Substring(0, appPath.Length - 1);
                }

                if (File.Exists(String.Format("{0}\\Log.txt", appPath)))
                {
                    using (StreamWriter file = new StreamWriter(String.Format("{0}\\Log.txt", appPath), true))
                    {
                        if (!isnewline) file.WriteLine();
                        file.WriteLine(String.Format("{0}", text));
                    }
                }
                else
                {
                    File.WriteAllText(String.Format("{0}\\Log.txt", appPath), text);
                }
            }
            catch (Exception) { }
        }
    }
}